Help with Find

[clayton74]

Hello all
I have a workbook with 10 worksheets,in column "a" in all worksheets I have various part numbers. Is there some vba I could use that will first select all worksheets and second have a find function to find a part number that may be entered in an input box

Thanks
Andy

 

[xlbo]

Try something like this
Sub Finder()
Dim mPartNo
mPartNo = InputBox("Enter Part Number&quot
For i = 1 To 10

Set foundcell = Sheets(i).Columns("A&quot.Find(mPartNo)
If foundcell Is Nothing Then
Else
MsgBox "Part Number " & mPartNo & " found on sheet " & Sheets(i).Name & " in cell " & foundcell.Address
Exit Sub
End If
Next i
MsgBox "Part Number " & mPartNo & " not found"
End Sub

HTh
Geoff

 

[clayton74]

Works grand thanks Geoff. I wish to go to the worksheet/cell that is displayed in msgbox. I apologise if I am asking to much

Andy

 

[xlbo]

ok
Sub Finder()
Dim mPartNo
mPartNo = InputBox("Enter Part Number&quot
For i = 1 To 10

Set foundcell = Sheets(i).Columns("A&quot.Find(mPartNo)
If foundcell Is Nothing Then
Else
sheets(i).select
range(foundcell.address).select
Exit Sub
End If
Next i
MsgBox "Part Number " & mPartNo & " not found"
End Sub

HTH
Geoff

 

[clayton74]

You are a god amongst gods thanks for your assistance