RE: Using FindNext to edit records instead of a Do Loop

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
877
Office Version
  1. 365
Platform
  1. Windows
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.
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.
Capture.JPG
cr
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
The usual idea here is to save off the first location from the FIND:

VBA Code:
Set FirstFind = ...

Then when you iterate the FindNext routine, you check for it:

VBA Code:
Set FindCell = ...
If FindCell Is FirstFind Then Exit Sub
 
Upvote 0
The usual idea here is to save off the first location from the FIND:

VBA Code:
Set FirstFind = ...

Then when you iterate the FindNext routine, you check for it:

VBA Code:
Set FindCell = ...
If FindCell Is FirstFind Then Exit Sub
Thanks for such a quick reply:
Following your suggestions:
1. Dim firstaddress
2. Dim firstaddress as range
3. Dim firstaddress as Variant

FInd button code:
Private Sub FindCompany()
Set findcell = Sheets("Sheet2").Range("A:A").FIND(What:=x, LookIn:=xlValues)
If Not findcell Is Nothing Then
Set firstaddress = findcell.Address ---- at this line per your suggestion
= generates errors:

1. = type mismatch
2. = object required
3. = type mismatch

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 findcell Is firstaddress Then Exit Sub '---where I put your 2nd line in FindNext button

Obviously, I'm doing something wrong. What am I missing here?
Thx again for all your help
cr
 
Upvote 0
It looks like you're mixing up an object (firstaddress) with a string (findcell.address). Try something like this:

VBA Code:
Public FirstAddr As String
Public FindCell As Range

Sub test1()

    Set FindCell = Range("A1:A20").Find("K2")
    FirstAddr = FindCell.Address
    
End Sub

Sub test2()

    Set FindCell = Range("A1:A20").Find("K2", FindCell)
    If FindCell.Address = FirstAddr Then
        MsgBox "No more"
    End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top