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
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