View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
excelnut1954 excelnut1954 is offline
external usenet poster
 
Posts: 175
Default Problem coding NextFind

Thanks Jim for replying again. Here's what I've done with your
code, and also what I've gotten when I run it:
I put the 1st 3 lines you gave me in the Declarations of a Standard
Module
Private rngToSearch As Range
Private rngFound As Range
Private strFirst As String

Note:
I also already have
Public FindPOVal As String
in the same module declaration. This is the variable name I use later
in the initial Find when the user 1st enters the PO number he wants to
search for. UserForm12
This sub is in UserForm12
Private Sub TextBox1_Change()
FindPOVal = TextBox1.Value
End Sub

This is in the 1st userform , UserForm12, that asks the user for the
PO number he wants to search for. After the PO is entered, it brings up
UserForm13, which shows the record

In the 1st 2 lines of your Sub FindFirst() code:
Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _

I inserted the worksheet name "Official List" in place of
"Sheet1", and put a "J" in for the Columns variable (that's
the column that contains the PO numbers).
My question on this section of coding is where you have "This"......
at the end of the Set rngFound statement.
In the code where I was trying to use FindNext before, from the Help
example, I was putting "FindPOVal" in there because that was the
variable name I'm using in the initial Find.
FindPOVal = textbox1
Should I replace "This", with "FindPOVal" ? Is this variable still
valid with the code you wrote?

Next, I put the 2 subs you wrote FindFirst & FindNext, in a Standard
Module
What I did next was to set up the command button (the Find Next Record
button that is within UserForm13, that will already be up from the
initial Find.

In the Command Button Click sub, I have my previously written code
that will Find the PO requested. There is an IF-Then-Else as part of
this coding. The Else part of the statement (if the PO was found) would
unload UserForm12, and bring up UserForm13 that would show all the data
in the PO found. Again, up to this part has worked fine.
It then goes to...FindFirst

So, it looks like this:
Private Sub CommandButton3_Click()
(lines of previously written coding described above.)
FindFirst
End Sub

Starting from the beginning:
I clicked the button on the worksheet to search for a record.
Userform12 comes up, asking for the PO number. I enter in a PO number
that I know is in more than 1 record. I click OK, and Userform13 comes
up, showing the record requested. This part of the whole thing has
always worked fine. Then, I click on the Find Next Record button in
that userform, and it says "Sorry Nothing to find", which is part of
your code.

The whole thing ran without any errors, after I set it up as I
described above. That's the positive. I just am not getting the
wanted results, which obviously means I didn't set it up correctly.

I hope I laid this out clear enough. I guess I don't understand how
the sub FindFirst is suppose to work, and how it relates to the
FindNext sub. I don't see how FindNext is ever called on to find the
2nd record. Does the "FindPOVal" variable reference I put in your
coding affect this?

If you can take the time to cipher my gibberish here, I would really
appreciate it.
Thanks again for your time & patience.
J.O.