FindPrevious - will not step back to the original Find.

[excelnut1954]

The 3 subs below work fine except that when I try to FindPrevious, it
will step back to each record found, except for the original record,
the initial Find.
If there are 4 records found, I can look at all 4 via the FindNext. I
can also go back and forth to see records 2 thru 4. But, it will not
FindPrevious back to the original record.

Any suggestions?
Thanks,
J.O.

Module Declarations:
Public rngToSearch As Range
Public rngFound As Range
Public strFirst As String
Public FindPOVal As String

************************************************
Sub TestFind_POCurrent()
Worksheets("Official List").Activate
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "This record was not found."
Unload UserForm12
UserForm12.Show
Else

strFirst = rngFound.Address
rngFound.Select

Unload UserForm12
UserForm13.Show
End If

End Sub

*******************************************
Sub TestFindNext_POCurrent()
Worksheets("Official List").Activate
Dim rngCurrent As Range
Set rngCurrent = rngToSearch.FindNext(rngFound)
If rngCurrent.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."
Else
Set rngFound = rngCurrent
rngFound.Select
Unload UserForm13
UserForm13.Show
End If

End Sub

**********************************************
Sub TestFindPrevious_POCurrent()
Worksheets("Official List").Activate
Dim rngCurrent As Range
Set rngCurrent = rngToSearch.FindPrevious(rngFound)
If rngCurrent.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."

Else
Set rngFound = rngCurrent
rngFound.Select
Unload UserForm13
UserForm13.Show
End If
End Sub

 

[Greg Wilson]

The following are abbreviations used in my post:
TestFind_POCurrent = TFind
TestFindNext_POCurrent = TFNext
TestFindPrevious_POCurrent = TFPrev.

TFPrev doesn't select the original record because TFind sets strFirst to the
address of the original record and TFPrev specifies that a message box will
be displayed instead of selecting the original record:

Set rngCurrent = rngToSearch.FindPrevious(rngFound)
If rngCurrent.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."
Else...

My read is that TFNext also doesn't select the original record. Rather,
TFind selects the original record and TFNext then only selects subsequent
records. Else, TFNext would loop around and select the original record. The
code is essentially the same as for TFPrev:

Set rngCurrent = rngToSearch.FindNext(rngFound)
If rngCurrent.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."
Else...

Suggested rewrites follow. Note that I do not need the usual "If Not
rngFound Is Nothing Then" construct because these two routines (apparently)
are only run after TFind is run and presumably only if TFind finds a record.
If there are no other records then they will just find the original record
instead of invoking an error. If this is not the case - i.e. if they can be
run before TFind or if TFind fails to find a record - then you will need to
make the adjustment. Minimal testing:

Sub TestFindNext_POCurrent()
Worksheets("Official List").Activate
Set rngFound = rngToSearch.FindNext(rngFound)
rngFound.Select
If rngFound.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."
Else
Unload UserForm13
UserForm13.Show
End If
End Sub

Sub TestFindPrevious_POCurrent()
Worksheets("Official List").Activate
Set rngFound = rngToSearch.FindPrevious(rngFound)
rngFound.Select
If rngFound.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."
Else
Unload UserForm13
UserForm13.Show
End If
End Sub

Regards,
Greg

 

[Greg Wilson]

In addition, unloading and reloading UserForm13 seems like a clunky way to do
whatverer is being achieved. Not to say I havn't ever resorted to such. It
might be the simpliest solution but I think this could likely be improved.

Regards,
Greg

 

[excelnut1954]

I don't disagree at all with your view on the method I'm using. I know
my coding is crude, but I'm learing as I go. Reloading UF13 is just a
way to repopulate the fields with the next record. I'm certain that
experienced programmers like yourself would gag if you read all of my
coding.

I would like to be able to rely on more efficient coding. But, I can't
use what I don't know yet. A little 2 day in-house class on VBA, and
I'm on my own for the rest. As I've learned more, I've gone back and
re-written code. I've been doing this since July, but it's not the main
part of my job. I just wanted to automate some of the spreadsheets I've
designed so people who don't know Excel that well can still interact
with what we have.

I appreciate your help, and comments.
J.O.

 

[Tom Ogilvy]

If your initialize event of the userform populates based on the selected
cell, then unloading and loading seems like a reasonable approach to me as
long as your doing it from an external procedure as you appear to be doing.

It is pretty much instantaneous if it is any slower at all and even if there
is a slight flicker, I view that as good feedback - lets the user know
something has changed.

 

[excelnut1954]

I had a chance to try out the code changes Greg offered. I had the same
problem with the Previous, where it wouldn't go back to the original
record found, during Find. I only got the message from the message box
in the code, showing nothing was found.
So, I diabled the If-Then-Else part of the Previous coding, and it
works.
Worksheets("Official List").Activate
Set rngFound = rngToSearch.FindPrevious(rngFound)
rngFound.Select
'If rngFound.Address = strFirst Then
'MsgBox "There are no other records with this PO/PL."

'Else
Unload UserForm13
UserForm13.Show
'End If

The only thing now is that hitting my Previous button over and over
will toggle to all the records, without end. It won't stop at the
original record.

This situation is better than it was. However, I suppose I really want
it to stop at the original record. Another quick-fix on my part.

ALSO: When UF13 comes up, it shows the total records found under the
requested number (PO#). The code for that textbox is below. Is there a
way for that number to change with each record? Ex: 4 records found.
number 1 is in the textbox. Clicking Next will show the next record,
and the number in the textbox will change to two. Next record will
produce 3 in that textbox. Then when hitting the Previous button, that
number in the textbox changes accordingly. If I could do that, I think
I would want to show in 2 seperate textboxes, the total found, and the
other box would show which record of that set is being viewed. 1 of 4,
2 of 4, etc.

Hope that makes sense.

'Counter for how many of this found POs there are on the list
'This goes in TextBox15
CountPO = Application.CountIf(Range("J:J"), FindPOVal)
TextBox15.Value = CountPO

Thanks Greg & Tom for your help

J.O.