lickrob
11-01-2012, 07:43 AM
Ok so i have a document simmilar to the document atatched and i need to search through it, the document its self is fairly large and as such is on 8 sheets.
I need my VBA code to search all the sheets however i can only seem to get it to search the first two.
below is the code that does not seem to be working:
Sub testsearch2()
Dim enCons As String
Dim serFor As String
Dim i As Integer
Dim sh As String
Dim Rng As Range
Dim ws As Worksheet
i = 1
sh = "Sheet" & i
serFor = InputBox("Search For: ")
testp:
i = i + 1
sh = "Sheet" & i
'Sheets("Sheet2").Select
Sheets(sh).Select
On Error GoTo testp:
Cells.Find(What:=serFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Do While ActiveCell.Value = "No"
Cells.Find(What:=serFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Loop
If ActiveCell.Value = "Yes" Then
Selection.End(xlUp).Select
enCons = ActiveCell.Text
MsgBox (enCons)
'Exit Do
Else
MsgBox ("no valid LOA")
End If
End Sub
the problem i am having is it will only loop once iwhen the error come up when teh search function dows not find a result. as in in will only search the first 2 sheets.
please help i have been stuck on this for 2 days now :(
I need my VBA code to search all the sheets however i can only seem to get it to search the first two.
below is the code that does not seem to be working:
Sub testsearch2()
Dim enCons As String
Dim serFor As String
Dim i As Integer
Dim sh As String
Dim Rng As Range
Dim ws As Worksheet
i = 1
sh = "Sheet" & i
serFor = InputBox("Search For: ")
testp:
i = i + 1
sh = "Sheet" & i
'Sheets("Sheet2").Select
Sheets(sh).Select
On Error GoTo testp:
Cells.Find(What:=serFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Do While ActiveCell.Value = "No"
Cells.Find(What:=serFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Loop
If ActiveCell.Value = "Yes" Then
Selection.End(xlUp).Select
enCons = ActiveCell.Text
MsgBox (enCons)
'Exit Do
Else
MsgBox ("no valid LOA")
End If
End Sub
the problem i am having is it will only loop once iwhen the error come up when teh search function dows not find a result. as in in will only search the first 2 sheets.
please help i have been stuck on this for 2 days now :(