E
excelnut1954
I have a UserForm that will show all the data from a record based on a
PO# the user enters. It worked fine, but it was designed rather sloppy.
I wanted to incorporate a FindNext, and had problems. I ended up
replacing the Find coding based on some valuable help from Jim
Thomlinson. I've spent the last couple days learning how to
understand this different method.
Here's what I have:
These lines are in the Declarations of a standard module.
Private rngToSearch As Range
Private rngFound As Range
Private strFirst As String
Public FindPOVal As String
This sub is in the same module. (I also have a FindNext sub in there,
also from Jim, but
I need to work this problem out first.)
Sub FindFirst()
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry Nothing to find"
Else
strFirst = rngFound.Address
Unload UserForm12
UserForm13.Show
End If
End Sub
UserForm12 that asks the user for the PO# to search has the OK button
that directs the flow to FindFirst above.
The problem I'm having at this point is that I don't know the
command to get the cursor in the correct position at the end of the
Find routine. As a result, the coding I have in UserForm13 to populate
the fields with the data for the PO# just found is incorrect. Below is
part of the coding I have in UserForm13 that will populate the fields.
This is based on the assumption that the cursor is starting at the
correct cell position, which is in the cell containing the PO# just
found. Again, the coding below is probably crude. But, in my old
design, it worked fine.
'PO# to TextBox1.
TextBox1.Value = FindPOVal
'Row to TextBox14. Moves cursor 8 cells to the left.
ActiveCell.Offset(0, -8).Select
TextBox14.Value = ActiveCell.Value
'Row1 to TextBox10. Moves cursor 1 cell to the right.
ActiveCell.Offset(0, 1).Select
TextBox10.Value = ActiveCell.Value
There are more of the same to populate 12 other fields.
I need to get the cursor on the cell containing the PO# found during
the Find routine. That way, the coding immediately above will work.
I tried inserting a few different lines in the FindFirst sub, such as
ActiveWindow.ActiveCell = strFirst without success.
Does anyone have any suggestions?
Thanks,
J.O.
PO# the user enters. It worked fine, but it was designed rather sloppy.
I wanted to incorporate a FindNext, and had problems. I ended up
replacing the Find coding based on some valuable help from Jim
Thomlinson. I've spent the last couple days learning how to
understand this different method.
Here's what I have:
These lines are in the Declarations of a standard module.
Private rngToSearch As Range
Private rngFound As Range
Private strFirst As String
Public FindPOVal As String
This sub is in the same module. (I also have a FindNext sub in there,
also from Jim, but
I need to work this problem out first.)
Sub FindFirst()
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry Nothing to find"
Else
strFirst = rngFound.Address
Unload UserForm12
UserForm13.Show
End If
End Sub
UserForm12 that asks the user for the PO# to search has the OK button
that directs the flow to FindFirst above.
The problem I'm having at this point is that I don't know the
command to get the cursor in the correct position at the end of the
Find routine. As a result, the coding I have in UserForm13 to populate
the fields with the data for the PO# just found is incorrect. Below is
part of the coding I have in UserForm13 that will populate the fields.
This is based on the assumption that the cursor is starting at the
correct cell position, which is in the cell containing the PO# just
found. Again, the coding below is probably crude. But, in my old
design, it worked fine.
'PO# to TextBox1.
TextBox1.Value = FindPOVal
'Row to TextBox14. Moves cursor 8 cells to the left.
ActiveCell.Offset(0, -8).Select
TextBox14.Value = ActiveCell.Value
'Row1 to TextBox10. Moves cursor 1 cell to the right.
ActiveCell.Offset(0, 1).Select
TextBox10.Value = ActiveCell.Value
There are more of the same to populate 12 other fields.
I need to get the cursor on the cell containing the PO# found during
the Find routine. That way, the coding immediately above will work.
I tried inserting a few different lines in the FindFirst sub, such as
ActiveWindow.ActiveCell = strFirst without success.
Does anyone have any suggestions?
Thanks,
J.O.