FindNext or SearchNext VBA Help

bettygoodspeed

New Member
Joined
Aug 31, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,
I created a basic data entry form. I didnt use the insert userform, instead I just used a small macro to allow the user to enter data into the 'form' and then have it populate the spreadsheet on another tab. (so they dont mess up the main data).

I want to allow the users to be able to search by name but we do have some duplicate records (intentionally). Ideally, they could click the search button and enter Smith, then if its not the right Smith, they can click next or something like that. I have been trying for days to get this darn thing to work. My code for the search is below... I'm so grateful for any assistance. I've googled and googled but no luck. Please help. Thank you so much.

'search box
searchvalue = sourcesheet.Range("C8").Value

'Is Name in there already?
If searchvalue <> vbNullString Then

'search code
Set Rng = dataidcol.find(what:=searchvalue, _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)


If Not Rng Is Nothing Then
'if yes
nextrow = Rng.Row
Else
'input new record
nextrow = datasheet.Range("G" & datasheet.Rows.Count).End(xlUp).Offset(1).Row

End If
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,514
Office Version
  1. 2013
Platform
  1. Windows
Not sure I fully understand what your code is doing, but the code below would allow a user to see what is in the found cell and the cell to the right of the found cell then decide if it is correct. If not it will then look for the search item in a different location. If no other target is found or if the target is found then the code would continue to the next step.
VBA Code:
Dim adr As String, ques As Variant, desc As String
'search box
 searchvalue = sourcesheet.Range("C8").Value
 'Is Name in there already?
    If searchvalue <> vbNullString Then
        'search code
        Set rng = dataidcol.Find(what:=searchvalue, _
        LookIn:=xlValues, lookat:=xlWhole, _
        searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rng Is Nothing Then
        'if yes
            adr = rng.Address
            Do
                desc = rng.Value & " " & rng.Offset(, 1).Value
                ques = MsgBox(desc & vbLf & "Is This The Correct Target?", vbQuestion + vbYesNo, "EVALUATE")
                If ques = vbYes Then Exit Do
                Set rng = dataidcol.FindNext(rng)
            Loop While rng.Address <> adr
 

Watch MrExcel Video

Forum statistics

Threads
1,112,939
Messages
5,543,119
Members
410,583
Latest member
jgalin
Top