Find and GoTo with Loop and Input Box

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
I know this code is messed up and was hoping for some help. I am trying to write a code that would allow the user to input a Parcel,Lot# and want to search 10 Worksheets named Data 1 thru 10 in one workbook. The matching records should be found in range AF of each sheet, if the record exists. If the matching record is found, I want the user to be able to input data into the worksheet, then have the option to search for another lot and repeat. If the record does not exist, then the user should have the option to search for another record.

Please do not get sick over my lame attempt. Thanks for the help.
Mike

Code:
Sub findtest()
Dim wks As Worksheet
Dim rng As Range
Dim StrAddress As String, StrFind As String

Do
StrFind = InputBox("Please enter search parcel,lot") & "Stakeout property corners, bldg corners, elevation"
'Range AF of each data sheet will contain a match for the StrFind
For i = 1 To 10

With Worksheets("Data" & i) '.Range("AF5:AF" & LRow)
LRow = Worksheets("Data" & i).Range("A65536").End(xlUp).Row
    For Each Ce In Worksheets("Data" & i).Range("AF5:AF" & LRow)
    Set rng = Worksheets("Data" & i).Range("AF5:AF" & LRow).Find(StrFind, lookat:=xlWhole, LookIn:=xlValues)
        If Not rng Is Nothing Then
            Application.Goto Reference:=rng, Scroll:=True
                rng.Offset(0, 20).Select
                     
              If MsgBox("Seach for another Lot?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
                
            ElseIf MsgBox("Could not find Lot, Search for another Lot?", vbYesNo + vbQuestion) = vbNo Then Exit Sub    'if not match found
        
        
        End If
        Next Ce
End With
Next i
Loop
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -
try;
Code:
Option Explicit
Sub find_me()
Dim i As Long
Dim inp, inp1, sht, n As String
Dim c As Object
On Error Resume Next
start:
inp = InputBox("Please enter search parcel,lot")
inp1 = inp & "Stakeout building corners"
For i = 1 To 10
sht = "Data" & i
    With Sheets(sht).Columns("AF")
        Set c = .Find(inp1, , , xlWhole)
            If Not c Is Nothing Then
                Sheets(sht).Select
                    c.Offset(, 20).Select
                        n = 1
                        If MsgBox("Seach for another Lot?", vbYesNo + vbQuestion) = vbYes Then
                            GoTo start:
                            Else
                                Exit Sub
                        End If
            End If
    End With
Next
If n <> 1 Then
    If MsgBox("Could not find Lot, Search for another Lot?", vbYesNo + vbQuestion) = vbNo Then
        Exit Sub    'if not match found
            Else
                GoTo start:
    End If
End If
End Sub
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
Thanks for the help. I wanted the string from the input box to combine with additional text, "Stakeout building corners..", so the string that we would be searching for would be, for example, "a18Stakeout building corners". The find function in excel does not seem to recognize this string either, which leads me to believe there must be a limitation on the find function? Should I the match function instead?

Mike
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Mike -

I have modified the code. Would you like to try again?
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134

ADVERTISEMENT

Thanks for the help. inp1 seems to calculate correctly when I hover over the code in break mode, but it is not "finding" the matching text in Column AF.

Mike
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
post a few sample of your data. what data examples are there in columnAF.
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134

ADVERTISEMENT

This minor change solves the problem. The find string in the worksheet is derived from a formula. I am not sure what the default variants are for find, but setting the variant to xlValues works.

Code:
 Set c = .Find(inp1, , LookIn:=xlValues, Lookat:=xlWhole)

My second question to this problem was, can the input box be programmed to allow the user to scroll through the worksheet and input data once the data is found, then click OK to search for the next lot?


I appreciate your patience working through this.

Mike
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
My second question to this problem was, can the input box be programmed to allow the user to scroll through the worksheet and input data once the data is found, then click OK to search for the next lot?

sorry, I could not understand fully. you mean when the input box pop-up, you want the user to select manually the data that needs to type in input box?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
My second question to this problem was, can the input box be programmed to allow the user to scroll through the worksheet and input data once the data is found, then click OK to search for the next lot?
Mike,
If I understand - that you want to have the inputbox remain showing and allow the user
to interact with the sheet at the same time - (yes?) then I believe the answer is no.
You could however use a userform for this.
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
Yes, it sounds like I will have to set up a user form-- my learning experience continues. Thanks for the help.
Mike
 

Forum statistics

Threads
1,136,510
Messages
5,676,281
Members
419,617
Latest member
Shane50GT

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