Problem coding NextFind

[excelnut1954]

This is an attempt to execute a FindNext.
The user enters a PO# in a UserForm, and after clicking OK, UserForm13
comes up, with all the data pertaining to that PO. That works fine. I
put another button in that form to request to Find the Next Record.
with the same PO, if there is any.

That button's code if below. What I want is for the cell that
contains that next PO to be named "EditPO".

Then, I want to unload the present UserForm13 (which has the data from
the 1ts Find). Then reload UserForm13, which will show the data for the
next record.

Is there something I can do to make the Range Name Add statement below
work within
this procedure? Or, is it plain I'm not going about this correctly?
I tried playing around with different ways to reproduce the "C."
code in the line where I'm trying to name the range.

Thanks for any help you can offer.
J.O.

Private Sub CommandButton3_Click()
'Find Next Record

With Worksheets("Official List").Range("j6:j65536")
Set C = .Find(FindPOVal, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
'C.Interior.Pattern = xlPatternGray50 (this line is from
the Help Example - left here as reference while figuring out how to
code my next command line, to name range, see next line.)

ActiveWorkbook.Names.Add Name:="EditPO",
RefersTo:=FoundCell
Unload UserForm13
UserForm13.Show

Set C = .FindNext(C)

Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
End Sub

 

[Jim Thomlinson]

IMO you are going about this a little bit wrong. Find next is great for
looping through all of the possible values that you can find. You however are
just trying to find the value after the one you just found. You should use a
plain old fashioned find, not a findnext. Find has an argument LookAfter:=???
Set that to the value that you originally found (offset by one) and then you
are off to the races as it will start it's search just after the cell that
you found...

 

[excelnut1954]

I'm very confused now. I'll try it again.

User types in PO# 1234 to find that record.
PO# 1234 comes up in a userform (via Find). This works good. This is
all done. The coding works fine.

Now, what if there is another PO# 1234 on the list?
Wouldn't I use FindNext to access that one?
I was thinking that it would run thru Find on the 1st look, then if
there is an exact same PO# on the list, then it should go thru FindNext
to locate THAT one. And, any other records with the same exact PO#.

The user won't know if there are more than 1 record for any one PO#.

I'm 1st trying to get FindNext to work right, and then I will address
the problem of coding to let the user know that there ARE other records
with the same PO. I'll go through that problem later.

1st, I want to get this done where it will recognize the 2nd, 3rd, 4th,
etc of all the same PO#s on the list.

Am I still wrong is thinking FindNext is what I need here?

Thanks for taking the time Jim
J.O.

 

[Dave Peterson]

How about an alternative?

Have the userform that knows the po number do the next/previous stuff. So the
first userform asks the user for the po#. Validates it. And the passes the
processing to the second userform.

I created a couple of userforms (userform1 and userform2).

On userform1, I had two buttons, a textbox and a label (for error messages).

On userform2, I had 3 buttons (next, previous, done), a label (for messages) and
3 textboxes--just to display some of the fields on that row.

I put this in a General module:

Option Explicit
Public myRngToCheck As Range
Public MaxMatches As Long
Sub LoadMyRngToCheck()
With Worksheets("Official List")
Set myRngToCheck = .Range("j6", .Cells(.Rows.Count, "J").End(xlUp))
End With
End Sub
Sub showIt()
UserForm1.Show
End Sub

This was behind userform1:
Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Me.Label1.Caption = ""

If Trim(Me.TextBox1.Value) = "" Then
Beep
Me.Label1.Caption = "Please enter a nice PO number"
Exit Sub
End If

If myRngToCheck Is Nothing Then
Call LoadMyRngToCheck
End If

MaxMatches = Application.CountIf(myRngToCheck, Me.TextBox1.Value)

If MaxMatches = 0 Then
'no match
Beep
Me.Label1.Caption = "No Matching PO"
Exit Sub
End If

Me.Hide
UserForm2.Show
Me.TextBox1.Value = ""
Me.Show

End Sub

Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Show PO"
End Sub


This was behind userform2:

Option Explicit
Dim CurMatch As Long
Dim CurCell As Range
Private Sub CommandButton1_Click()
'Next
If CurMatch = MaxMatches Then
Beep
Else
With myRngToCheck
Call DoNextPreviousFind(xlNext)
CurMatch = CurMatch + 1
Call LoadTheForm
End With
End If
End Sub
Private Sub CommandButton2_Click()
'Previous
If CurMatch = 1 Then
Beep
Else
With myRngToCheck
Call DoNextPreviousFind(xlPrevious)
CurMatch = CurMatch - 1
Call LoadTheForm
End With
End If
End Sub
Private Sub CommandButton3_Click()
'Done
Unload Me
End Sub
Private Sub UserForm_Initialize()
With myRngToCheck
Set CurCell = .Cells(.Cells.Count)
Call DoNextPreviousFind(xlNext)
End With

Me.CommandButton1.Caption = "Next"
Me.CommandButton2.Caption = "Previous"
Me.CommandButton3.Caption = "Done"

CurMatch = 1

Call LoadTheForm
End Sub
Private Sub LoadTheForm()
Me.Label1.Caption = Format(CurMatch, "#,##0") & " of " _
& Format(MaxMatches, "#,##0")

Me.TextBox1.Value = CurCell.Value
Me.TextBox2.Value = CurCell.Offset(0, 1).Value
Me.TextBox3.Value = CurCell.Offset(0, 2).Value

End Sub
Private Sub DoNextPreviousFind(myDirection As Long)
With myRngToCheck
Set CurCell = .Cells.Find(what:=UserForm1.TextBox1.Value, _
after:=CurCell, LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=myDirection, MatchCase:=False)
End With
End Sub


Maybe you can use use part of it.

 

[Jim Thomlinson]

Sorry I read your question ever so slightly incorrect. Give this a try...

Private rngToSearch As Range
Private rngFound As Range
Private strFirst As String

Sub FindFirst()
Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Sorry Nothing to find"
'Disable the findnext button
Else
strFirst = rngFound.Address
MsgBox rngFound.Address
'Enable the findnext button
End If

End Sub

Sub FindNext()
Set rngFound = rngToSearch.FindNext(rngFound)
If rngFound.Address = strFirst Then
MsgBox "You are back at the beginning"
'Disable the find next button
Else
MsgBox rngFound.Address
End If
End Sub

 

[excelnut1954]

Thanks Jim for replying again. I still have questions. I admire your
knowledge of VBA. As I do of all the people who've replied to my
questions. But, I am lost.

I'm not sure where you're intending your code should go.
*******************************
The 1st 3 lines of code you wrote:
Private rngToSearch As Range
Private rngFound As Range
Private strFirst As String

Where do they go? In the Declarations of the UserForm where my Find
Next Record button is located?
I see it's before
Sub FindFirst()
So, I don't understand how those 1st 3 lines of code get read.

************************************
Also, is all the code you gave me to be inside the button's Click
Sub? That is, I currently have
Private Sub CommandButton3_Click() This is the Find Next Record
button that is in the UserForm that will already be up (this is the
UserForm that shows the results of the initial Find).
Or, are these 2 subs you wrote called from my Private Sub
CommandButton3_Click().
Or, maybe just the 1st one is called from there......

*******************************
Also, 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). But, where you have
"This"...... in my code, I was putting "FindPOVal" in there
because that was the variable name I used in the initial Find.
FindPOVal = textbox1 when the user 1st entered the PO number he
wanted to search for.
Should I replace "This", with "FindPOVal" ?
*****************************

I may never understand this. But, I at least want to try by putting the
code in the correct places. Then, with reverse engineering, I can learn
what's happening. That's how I've learned much of what I know
about VBA.

Thanks for your time & patience, Jim.

J.O.

 

[excelnut1954]

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.

 

[excelnut1954]

I sure appreciate the effort, Dave. I just don't know enough about VBA
to tackle all of that.

All I really want is to find the Next record (if there is one), so I
can givie it a range name. I already have a macro to do the initial
Find, and it works fine.

Anyway, always appreciate any help I can get here. I'm going to copy
your code, and try to figure it all out. Probably piece by piece as I
learn more.

Thanks again, Dave.

J.O.

 

[Dave Peterson]

I didn't see a reason to use a name.

But there isn't too much in that code, but post back if you have questions.