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

Discussion in 'Excel Programming' started by L. Howard, May 6, 2014.

  1. L. Howard

    L. Howard Guest

    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
     
    L. Howard, May 6, 2014
    #1
    1. Advertisements

  2. L. Howard

    GS Guest

    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
     
    GS, May 6, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    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
     
    L. Howard, May 6, 2014
    #3
  4. L. Howard

    GS Guest

    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, May 6, 2014
    #4
  5. L. Howard

    GS Guest

    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
     
    GS, May 6, 2014
    #5
  6. L. Howard

    L. Howard Guest

    > >
    >
    > > 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
     
    L. Howard, May 6, 2014
    #6
  7. L. Howard

    GS Guest

    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, May 6, 2014
    #7
  8. L. Howard

    GS Guest

    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
     
    GS, May 6, 2014
    #8
  9. L. Howard

    Claus Busch Guest

    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
     
    Claus Busch, May 6, 2014
    #9
  10. L. Howard

    GS Guest

    > 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
     
    GS, May 6, 2014
    #10
  11. L. Howard

    GS Guest

    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
     
    GS, May 6, 2014
    #11
  12. L. Howard

    L. Howard Guest

    Re: 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
     
    L. Howard, May 6, 2014
    #12
  13. L. Howard

    GS Guest

    Re: 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
     
    GS, May 6, 2014
    #13
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. L. Howard Kittle

    If no FIND value is found, then a Msgbox to say so and exit sub

    L. Howard Kittle, Sep 23, 2003, in forum: Excel Programming
    Replies:
    3
    Views:
    91
    L. Howard Kittle
    Sep 23, 2003
  2. MBlake

    MsgBox Code Error

    MBlake, Apr 28, 2005, in forum: Excel Programming
    Replies:
    8
    Views:
    128
    MBlake
    May 2, 2005
  3. Ouka

    syntax for code in MsgBox()?

    Ouka, Jul 19, 2005, in forum: Excel Programming
    Replies:
    3
    Views:
    76
    Patti
    Jul 20, 2005
  4. Alex Martinez

    Placing a code before Save & Save As

    Alex Martinez, Sep 27, 2005, in forum: Excel Programming
    Replies:
    0
    Views:
    90
    Alex Martinez
    Sep 27, 2005
  5. Old Dog

    Problem with Msgbox Code

    Old Dog, Nov 23, 2005, in forum: Excel Programming
    Replies:
    3
    Views:
    75
    Old Dog
    Nov 23, 2005
  6. gordom
    Replies:
    4
    Views:
    155
    gordom
    Mar 10, 2009
  7. JasonK
    Replies:
    4
    Views:
    97
    Don Guillett
    Jul 4, 2009
  8. Johan

    Placing picture with code

    Johan, Oct 24, 2010, in forum: Excel Programming
    Replies:
    1
    Views:
    89
    Master Blaster
    Oct 25, 2010
Loading...