Working on FindPrevious command in a userform

I'v recently learned how to implement the Find, and FindNext commands
in some userforms I've designed. They search a list of PO numbers,
and work ok. Below, I'll show the subs I have that use these
commands.

What I want to do now is to put in a FindPrevious button in these
userforms. That way, the user can toggle each way in the list of found
PO numbers. I tried to use the same concept of the FindNext, but it
doesn't work. I didn't expect it to. I think I may want to
re-design what I have to accomplish this better.

I'm thinking that what I may want to do at the point where I do the
initial Find for a PO number, is that any cell containing this PO
number would get a "name" (variable name? Can't think of the term
I want). Then, the FindNext would go to each of these names, and the
FindPrevious would go back one record.

I think if I can find out how to name all the cells that contain a PO
number during this initial Find, then I might be able to figure out the
rest, and how to insert this into the Find, FindNext, and FindPrevious
commands.

Here are the 2 subs I use the Find and FindNext commands.

Sub FindViaPOCurrent()
'This is for the PO/PL search via UserForm12. Clicking the OK button
'brings you here. If record found, it opens up UserForm13 to show
'that record. The "Find Another Record" button will also loop back
here.
Worksheets("Official List").Activate
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues)

If rngFound Is Nothing Then
MsgBox "This record was not found. Make sure you entered the
correct number." Worksheets("Menu").Activate
Unload UserForm12
UserForm12.Show
Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show

End If

End Sub

Sub FindNextViaPOCurrent()
'This is routine from clicking the "Get the next record w/ same PO..."
'button. If no duplicates found, you get message. If there is, it
'brings up UserForm13 like above.

Set rngFound = rngToSearch.FindNext(rngFound)
If rngFound.Address = strFirst Then
MsgBox "There are no other records with this PO/PL. Search for
a different PO/PL, or click Close"

Else
rngFound.Select
Unload UserForm13
UserForm13.Show

End If

End Sub

I would appreciate any help with this.
Thanks,
J.O.

You can create a range object of all of the found PO's if you wnat . That is
easy to do. Before we go their hwoever why exactly did FindPrevious not work?
Without trying it myself I do not see a reason why it wouldn't work...
--
HTH...

Jim Thomlinson


"excelnut1954" wrote:

> I'v recently learned how to implement the Find, and FindNext commands
> in some userforms I've designed. They search a list of PO numbers,
> and work ok. Below, I'll show the subs I have that use these
> commands.
>
> What I want to do now is to put in a FindPrevious button in these
> userforms. That way, the user can toggle each way in the list of found
> PO numbers. I tried to use the same concept of the FindNext, but it
> doesn't work. I didn't expect it to. I think I may want to
> re-design what I have to accomplish this better.
>
> I'm thinking that what I may want to do at the point where I do the
> initial Find for a PO number, is that any cell containing this PO
> number would get a "name" (variable name? Can't think of the term
> I want). Then, the FindNext would go to each of these names, and the
> FindPrevious would go back one record.
>
> I think if I can find out how to name all the cells that contain a PO
> number during this initial Find, then I might be able to figure out the
> rest, and how to insert this into the Find, FindNext, and FindPrevious
> commands.
>
> Here are the 2 subs I use the Find and FindNext commands.
>
> Sub FindViaPOCurrent()
> 'This is for the PO/PL search via UserForm12. Clicking the OK button
> 'brings you here. If record found, it opens up UserForm13 to show
> 'that record. The "Find Another Record" button will also loop back
> here.
> Worksheets("Official List").Activate
> Set rngToSearch = Sheets("Official List").Columns("J")
> Set rngFound = rngToSearch.Find(What:=FindPOVal, _
> LookIn:=xlValues)
>
> If rngFound Is Nothing Then
> MsgBox "This record was not found. Make sure you entered the
> correct number." Worksheets("Menu").Activate
> Unload UserForm12
> UserForm12.Show
> Else
> strFirst = rngFound.Address
> rngFound.Select
> Unload UserForm12
> UserForm13.Show
>
> End If
>
> End Sub
>
> Sub FindNextViaPOCurrent()
> 'This is routine from clicking the "Get the next record w/ same PO..."
> 'button. If no duplicates found, you get message. If there is, it
> 'brings up UserForm13 like above.
>
> Set rngFound = rngToSearch.FindNext(rngFound)
> If rngFound.Address = strFirst Then
> MsgBox "There are no other records with this PO/PL. Search for
> a different PO/PL, or click Close"
>
> Else
> rngFound.Select
> Unload UserForm13
> UserForm13.Show
>
> End If
>
> End Sub
>
> I would appreciate any help with this.
> Thanks,
> J.O.
>
>

This is close to what you want to do.. Note that there is now a rngcurrent in
each sub...

Dim strFirst As String
Dim rngToSearch As Range
Dim rngFound As Range

Private Sub CommandButton1_Click()
Set rngToSearch = Sheet1.Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rngFound Is Nothing Then
rngFound.Select
strFirst = rngFound.Address
End If

End Sub

Private Sub CommandButton2_Click()
Dim rngCurrent As Range
Set rngCurrent = rngToSearch.FindNext(rngFound)
If rngCurrent.Address = strFirst Then
'disable find next button
MsgBox "the end"
Else
'enable find previous button
Set rngFound = rngCurrent
rngFound.Select
End If

End Sub

Private Sub CommandButton3_Click()
Dim rngCurrent As Range
Set rngCurrent = rngToSearch.FindPrevious(rngFound)
If rngCurrent.Address = strFirst Then
'disable find previous button
MsgBox "the end"
Else
'enable find next button
Set rngFound = rngCurrent
rngFound.Select
End If
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> You can create a range object of all of the found PO's if you wnat . That is
> easy to do. Before we go their hwoever why exactly did FindPrevious not work?
> Without trying it myself I do not see a reason why it wouldn't work...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "excelnut1954" wrote:
>
> > I'v recently learned how to implement the Find, and FindNext commands
> > in some userforms I've designed. They search a list of PO numbers,
> > and work ok. Below, I'll show the subs I have that use these
> > commands.
> >
> > What I want to do now is to put in a FindPrevious button in these
> > userforms. That way, the user can toggle each way in the list of found
> > PO numbers. I tried to use the same concept of the FindNext, but it
> > doesn't work. I didn't expect it to. I think I may want to
> > re-design what I have to accomplish this better.
> >
> > I'm thinking that what I may want to do at the point where I do the
> > initial Find for a PO number, is that any cell containing this PO
> > number would get a "name" (variable name? Can't think of the term
> > I want). Then, the FindNext would go to each of these names, and the
> > FindPrevious would go back one record.
> >
> > I think if I can find out how to name all the cells that contain a PO
> > number during this initial Find, then I might be able to figure out the
> > rest, and how to insert this into the Find, FindNext, and FindPrevious
> > commands.
> >
> > Here are the 2 subs I use the Find and FindNext commands.
> >
> > Sub FindViaPOCurrent()
> > 'This is for the PO/PL search via UserForm12. Clicking the OK button
> > 'brings you here. If record found, it opens up UserForm13 to show
> > 'that record. The "Find Another Record" button will also loop back
> > here.
> > Worksheets("Official List").Activate
> > Set rngToSearch = Sheets("Official List").Columns("J")
> > Set rngFound = rngToSearch.Find(What:=FindPOVal, _
> > LookIn:=xlValues)
> >
> > If rngFound Is Nothing Then
> > MsgBox "This record was not found. Make sure you entered the
> > correct number." Worksheets("Menu").Activate
> > Unload UserForm12
> > UserForm12.Show
> > Else
> > strFirst = rngFound.Address
> > rngFound.Select
> > Unload UserForm12
> > UserForm13.Show
> >
> > End If
> >
> > End Sub
> >
> > Sub FindNextViaPOCurrent()
> > 'This is routine from clicking the "Get the next record w/ same PO..."
> > 'button. If no duplicates found, you get message. If there is, it
> > 'brings up UserForm13 like above.
> >
> > Set rngFound = rngToSearch.FindNext(rngFound)
> > If rngFound.Address = strFirst Then
> > MsgBox "There are no other records with this PO/PL. Search for
> > a different PO/PL, or click Close"
> >
> > Else
> > rngFound.Select
> > Unload UserForm13
> > UserForm13.Show
> >
> > End If
> >
> > End Sub
> >
> > I would appreciate any help with this.
> > Thanks,
> > J.O.
> >
> >

It was a sloppy attempt. I was kind of thinking of the concept I
described above. I just thought of putting in a previous button, and
read the help on it. I thought I had to go a little deeper than just
replacing variables in the 2 subs I had already.
Range object. That's what I was thinking of. Thanks for the code below.
I'll give it a try.
J.O.