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
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