Problem with cursor position after Find is run.

[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.

 

[Guest]

If I recall correctly you are searching the active sheet? If that is the case
then you just need to add

rngFound.Select

something like this...

If rngFound Is Nothing Then
MsgBox "Sorry Nothing to find"
Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show

 

[excelnut1954]

You're the best, Jim. Thanks!!

Now, I'll start on the FindNext part.....
I appreciate your help.
J.O.

 

[Guest]

Let me know how it goes...