VBA Search Multiple Sheets help

[lickrob]

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

[Bob Phillips]

What are you trying to do on each as you find the selected text?

[lickrob]

when it finds the selected text i need it to check the row for a value these vaules will be either 'Yes' or 'No' if its yes then it needs to get the value of the top cell in that column and display it if its no it should carry on searching the rest of the workbook untill it finds a yes or it has searched the entire workbook

[Bob Phillips]

All I can see are Yes and No on each sheet. Give an example of what it would search for , and if it found it on row 8 of sheet 9 what should it display. Ditto row 9.

[lickrob]

ok so for example you search for "name 5" it would display "company 19".

second example would be if you searc for "name 103" it should show "Company 103"

in both of these cases that is what it actually does. however if i where to search for "name 202" it should show "company 202" however it does not.

[GreenDR]

Try this

lol:
i = i + 1
sh = "Sheet" & i


'Sheets("Sheet2").Select
Sheets(sh).Select
Set Rng = Cells.Find(What:=serFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Rng Is Nothing Then
GoTo lol:
Else
Rng.Activate
End If

[lickrob]

Thats it, working like a charm. thanks alot.