Placing the MsgBox "No ID found" statement in my code??

[L. Howard]

I want to have a notification of "Not Found" if the ID number does not exist on any worksheet, except sheet 1, which is not searched.

I know where to put it if looking at only one sheet, but everywhere I tried it increments sheet by sheet as Not Found until an Id is found then increments on sheets past the found ID.

Thanks.
Howard

'Else
' MsgBox "No ID found"



Sub AllMySheets()

Dim ws As Worksheet
Dim FindString As String
Dim Rng As Range

FindString = InputBox("Enter a Client ID numbet")

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then

If Trim(FindString) <> "" Then

With ws
Set Rng = .UsedRange.Find(What:=FindString, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not Rng Is Nothing Then
MsgBox Rng.Address & " " & ws.Name
End If

End With

End If

End If
Next ws

End Sub

 

[GS]

Use a boolean 'flag' to let your code know whether an ID was found or
not...

Dim bFoundID As Boolean

...and add it to the part of code that finds an ID...

...<snip>
If Not Rng Is Nothing Then
bFoundID = True: MsgBox Rng.Address & " " & ws.Name
End If

...and just check its value...

...<snip>
Next ws
If Not bFoundID Then MsgBox "Not found"
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

 

[L. Howard]

Use a boolean 'flag' to let your code know whether an ID was found or

not...



Dim bFoundID As Boolean



..and add it to the part of code that finds an ID...



...<snip>

If Not Rng Is Nothing Then

bFoundID = True: MsgBox Rng.Address & " " & ws.Name

End If



..and just check its value...



...<snip>

Next ws

If Not bFoundID Then MsgBox "Not found"

End Sub

That worked well.

Thanks, Garry

 

[GS]

Well.., that was a 'quick-n-dirty' solution! Here's how I might handle
the same task...

Sub AllMySheets()
' 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 = ",'" & 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, s), ","), 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

 

[GS]

Typo fix...

Well.., that was a 'quick-n-dirty' solution! Here's how I might
handle the same task...

Sub AllMySheets()
' 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

sIdShts = ",'" & 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, s), ","), 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

 

[L. Howard]

sIdShts = ",'" & ws.Name & "'!" & rng.Address

Should this line be

sMsg = sMsg & Join(Split(Mid(sIdShts, s), ","), vbLf)

Be like this? (sIdShts, sID)

sMsg = sMsg & Join(Split(Mid(sIdShts, sID), ","), vbLf)


Does not include the sheet name in my test.

Howard

 

[GS]

Good catch! Should be...

sMsg = sMsg & Join(Split(Mid(sIdShts, 2), ","), vbLf)

...to remove the leading ","!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

And the following line...

sIdShts = ",'" & ws.Name & "'!" & rng.Address

..should be...

sIdShts = sIdShts & ",'" & ws.Name & "'!" & rng.Address

...so it preserves existing values!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Garry, hi Howard,

Am Tue, 06 May 2014 13:13:34 -0400 schrieb GS:

Good catch! Should be...

sMsg = sMsg & Join(Split(Mid(sIdShts, 2), ","), vbLf)

..to remove the leading ","!

if you want all matches be listed you must change:
sIdShts = sIdShts & ",'" & ws.Name & "'!" & rng.Address



Regards
Claus B.

 

[GS]

Hi Garry, hi Howard,

Am Tue, 06 May 2014 13:13:34 -0400 schrieb GS:


if you want all matches be listed you must change:
sIdShts = sIdShts & ",'" & ws.Name & "'!" & rng.Address



Regards
Claus B.

Hi Claus,
I guess you didn't see I already caught that one, but thanks just the
same!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

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

 

[L. Howard]

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

Works for me!!! Nice indeed.

Small typo on this line but quite fixable even by me.

sidhts = sidhts & ",'" & ws.Name & "'!" & rng.Address

I like. Thanks.

Howard

 

[GS]

Yeah, I just noticed that too! (It's my keypress problem being extra
exuberant today, plus I was at hospital when I wrote original code so
lots of interuptions breaking my focus<g>. I'm back home now!)

Fixed & tested...

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
sIdShts = sIdShts & ",'" & 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