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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
589
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,826
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
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
589
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
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,826
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,219
Messages
5,640,957
Members
417,183
Latest member
CuteLeo

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
Top