Should I use Do-While loop for my record counter?

E

excelnut1954

This macro will find a record via user input, then UserForm13 opens up,
showing the record. I recently added code to the sub attempting a Do
While loop. I've never written any of the Do loops before. I'm
doing this in an attempt to assign a number to each record found. I
want this so I can show that number in the userform in TextBox16, to
go with the number I already have there, which shows the total number
of records found.
(The code I have for TextBox16 in UserForm13 is below the sub code.) As
the Next button is clicked in the userform, the next record comes up. I
want TextBox16 to show that number assigned to it.

Obviously I don't have it quite right. There is no error when I run
this. However, the number in TextBox16 stays at 1 no matter which of
the found records I click to. For each record I click to, it should
show 2, 3, 4, etc.

I've thought that maybe I'm using the wrong statement. Maybe I
should be using
For-Each instead of Do-While.

Can someone please point me in the right direction?
Thanks,
J.O.


Sub TestFind_POCurrent()
Worksheets("Official List").Activate
RecordsFound = 0

'*****This is the initial search based on user input.
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. Make sure you entered the
correct number. Also, check the Deleted List."
Unload UserForm12 ' UF12 is where user inputs the PO number
UserForm12.Show
Else

strFirst = rngFound.Address
rngFound.Select
RecordsFound = 1
End If

'****my attempt to number each record found
Do While NumberNextRecord = True
Set rngFound = rngToSearch.FindNext(rngFound)
If rngFound Is Nothing Then
NumberNextRecord = False

Else
NumberNextRecord = True
RecordsFound = RecordsFound + 1
End If

Loop

Unload UserForm12
UserForm13.Show
End Sub

*****I thought that the above code would assign a sequential number to
each
record found... RecordsFound = RecordsFound + 1. Obviously its
only entering the number 1 for the term RecordsFound, and not adding 1
for each record.

********
'TextBox16 within UserForm13 shows the number assigned to this record
'This goes with TextBox15, which shows the total records found.

TextBox16.Value = RecordsFound
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top