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

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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.
 
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
 
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
 
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
 
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
 
Back
Top