Archangel117
08-09-2013, 01:05 PM
So what I am trying to do is to get my search code that I already have to search in more than one sheet. In reality it is only 2 sheets that need to be searched: Sheet1 and Sheet2. What I have works for one only for some reason and anything that I have tried to do to it to make it search both sheets causes error messages to pop up. So if there is someone that has an easy way to make my code search 2 sheets at once that would be a ton of help. Thanks for any help that I get!
Sub Find_First()
Dim FindString As String
Dim Rng As Range
Dim MyFind As Long
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("B2:D" & Range("D" & Rows.Count).End(xlUp).Row)
Set Rng = .Find(What:=FindString, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Rng Is Nothing Then
MyFind = Rng.Row
Do
Set Rng = .FindNext(Rng)
Select Case MsgBox("Found here:" & vbCrLf & Chr(9) & _
"Project " & Chr(9) & ": " & Range("A" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Part # " & Chr(9) & ": " & Range("B" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Description " & Chr(9) & ": " & Range("C" & Rng.Row).Value & vbCrLf & Chr(9) & _
"P.O " & Chr(9) & ": " & Range("D" & Rng.Row).Value & vbCrLf & Chr(9) & _
"P Card " & Chr(9) & ": " & Range("E" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Date " & Chr(9) & ": " & Range("F" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Units ordered " & Chr(9) & ": " & Range("G" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Status " & Chr(9) & ": " & Range("H" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Ordered By " & Chr(9) & ": " & Range("I" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Used on " & Chr(9) & ": " & Range("J" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Yes = Next" & Chr(9) & "No = Stop", vbInformation + vbYesNo, "Search string: " & FindString)
Case Is <> vbYes: GoTo exitLoop
End Select
Loop While Not Rng Is Nothing And Rng.Row <> MyFind
exitLoop:
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub
Sub Find_First()
Dim FindString As String
Dim Rng As Range
Dim MyFind As Long
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("B2:D" & Range("D" & Rows.Count).End(xlUp).Row)
Set Rng = .Find(What:=FindString, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Rng Is Nothing Then
MyFind = Rng.Row
Do
Set Rng = .FindNext(Rng)
Select Case MsgBox("Found here:" & vbCrLf & Chr(9) & _
"Project " & Chr(9) & ": " & Range("A" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Part # " & Chr(9) & ": " & Range("B" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Description " & Chr(9) & ": " & Range("C" & Rng.Row).Value & vbCrLf & Chr(9) & _
"P.O " & Chr(9) & ": " & Range("D" & Rng.Row).Value & vbCrLf & Chr(9) & _
"P Card " & Chr(9) & ": " & Range("E" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Date " & Chr(9) & ": " & Range("F" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Units ordered " & Chr(9) & ": " & Range("G" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Status " & Chr(9) & ": " & Range("H" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Ordered By " & Chr(9) & ": " & Range("I" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Used on " & Chr(9) & ": " & Range("J" & Rng.Row).Value & vbCrLf & Chr(9) & _
"Yes = Next" & Chr(9) & "No = Stop", vbInformation + vbYesNo, "Search string: " & FindString)
Case Is <> vbYes: GoTo exitLoop
End Select
Loop While Not Rng Is Nothing And Rng.Row <> MyFind
exitLoop:
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub