Final version:
Sub FindSheetsWithID()
' Looks for an ID on all sheets except "Sheet1",
' and notifies the result of the search.
Dim ws As Worksheet, rng As Range
Dim sID$, sIdShts$, sMsg$
Dim bFoundID As Boolean
sID = InputBox("Enter a Client ID numbet")
If Trim(sID) = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet1" Then
Set rng = ws.UsedRange.Find(What:=sID, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
If Not rng Is Nothing Then
bFoundID = True
sidhts = sidhts & ",'" & ws.Name & "'!" & rng.Address
End If
End If
Next ws
If bFoundID Then
sMsg = "The ID (" & sID & ") was found on the following sheets:"
sMsg = sMsg & vbLf & vbLf
sMsg = sMsg & Join(Split(Mid(sIdShts, 2), ","), vbLf)
Else
sMsg = "ID not found"
End If
MsgBox sMsg
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion