All variables Public:
Option Explicit
Dim Where As Range
This code is on "Find value" button that finds the first value of x in col A and assigns firstaddress to that first value found.
UF is just the abbreviated userform name. Three textboxes are populated with data from Sheet2 when UF opens.
This code is in the "Find Next" button and finds all values AFTER the first found value in col A assigned to findaddress.
The FindNext button works great - except the (after: =findcell). The code causes the execution to wrap back to the first value found = firstaddress and the whole process starts over. That solves nothing.
What I want is to be able to tell the FIndNext code in the FIndNext button to STOP when it returns to the first found value like Do Loop code. My logic was that by assigning it findaddress and writing code to stop when it reaches that first value found = firstaddress, it would then display
I cannot figure out how get the code to recognize that it is back at the first record and to stop there. PS: I have done this already with a Do Loop.
I prefer not to use a Do Loop because I want to be able to pause execution and make changes to each record found if I want to.
Having two buttons as I do now allows changes until the button is clicked again.
Can anyone please help me with this?
Thanks, anyone.
cr
Option Explicit
Dim Where As Range
This code is on "Find value" button that finds the first value of x in col A and assigns firstaddress to that first value found.
UF is just the abbreviated userform name. Three textboxes are populated with data from Sheet2 when UF opens.
Code:
Private Sub FindCompany ()
Set findcell = Sheets("Sheet2"). Range ("A: A"). FIND (What: =x, LookIn: =xlValues)
If Not findcell Is Nothing, Then
firstaddress = findcell. Address
' f = findcell. Row
UF. Text = f
UF. TextBox1.value = findcell (1, 1).value
UF. TextBox2.value = findcell (1, 2).value
UF. TextBox3.value = findcell (1, 3).value
This code is in the "Find Next" button and finds all values AFTER the first found value in col A assigned to findaddress.
Code:
Private Sub FindNextCompany ()
Dim Where As Range
Set Where = Sheets("Sheet2"). Range("A:A")
If Not findcell Is Nothing, Then
Set findcell = Where.FindNext(after: =findcell)
If Not findcell Is Nothing, Then
f = findcell. Row
UF. TextBox1.value = findcell (1, 1).value
UF. TextBox2.value = findcell (1, 2).value
UF. TextBox3.value = findcell (1, 3).value
Else
MsgBox "Value not found."
End If
End If
The FindNext button works great - except the (after: =findcell). The code causes the execution to wrap back to the first value found = firstaddress and the whole process starts over. That solves nothing.
What I want is to be able to tell the FIndNext code in the FIndNext button to STOP when it returns to the first found value like Do Loop code. My logic was that by assigning it findaddress and writing code to stop when it reaches that first value found = firstaddress, it would then display
Code:
MsgBox "End of search. No more records!"
I cannot figure out how get the code to recognize that it is back at the first record and to stop there. PS: I have done this already with a Do Loop.
I prefer not to use a Do Loop because I want to be able to pause execution and make changes to each record found if I want to.
Having two buttons as I do now allows changes until the button is clicked again.
Can anyone please help me with this?
Thanks, anyone.