Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
On Monday, May 5, 2014 10:30:02 PM UTC-7, GS wrote:
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 That worked well. Thanks, Garry |
Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
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 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 |
Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
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 |
Placing the MsgBox "No ID found" statement in my code??
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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Placing the MsgBox "No ID found" statement in my code??
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 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:
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:
On Tuesday, May 6, 2014 10:38:15 AM UTC-7, GS wrote:
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 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 |
Final version:
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 |
All times are GMT +1. The time now is 03:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com