Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find chars in SheetName (Excel 2003)

    Hi,

    I need to display all the sheets that contain the characters (M) in the name, and place them on a sheet. These characaters can be anywhere in the name and the name can be any length.
    (this macro also places hyperlinks on the names). I just about have it, but I do not have the correct syntax ... below is what I have.
    Again, your help is appreciated!
    --cat


    <code>
    Sub PrintSheetNames()
    N = ActiveWorkbook.Sheets.Count
    For i = 1 To N
    If ActiveWorkbook.Sheets(i).Visible Then
    'Males first
    If Find("(M)", ActiveWorkbook.Sheets(i).Name) = True Then
    Sheets("Input-General").Range("A" & 129 + i) = ActiveWorkbook.Sheets(i).Name
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & 129 + i), _
    Address:="", _
    SubAddress:="'" & Sheets(i).Name & "'!A129", _
    TextToDisplay:=Sheets(i).Name
    End If
    'Females next
    If Find("(F)", ActiveWorkbook.Sheets(i).Name) = True Then
    Sheets("Input-General").Range("D" & 129 + i) = ActiveWorkbook.Sheets(i).Name
    ActiveSheet.Hyperlinks.Add Anchor:=Range("D" & 129 + i), _
    Address:="", _
    SubAddress:="'" & Sheets(i).Name & "'!D129", _
    TextToDisplay:=Sheets(i).Name
    End If
    End If
    Next i
    Range("A" & 129 & ":A" & 129 + N).Sort Key1:=Range("A129")
    Range("D" & 129 & "" & 129 + N).Sort Key1:=Range("D129")
    End Sub
    </code>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find chars in SheetName (Excel 2003)

    Instead of
    <code>
    If Find("(M)", ActiveWorkbook.Sheets(i).Name) = True Then
    </code>
    use
    <code>
    If ActiveWorkbook.Sheets(i).Name Like "*(M)*" Then
    </code>
    or
    <code>
    If InStr(ActiveWorkbook.Sheets(i).Name, "(M)") > 0 Then
    </code>
    The Like operator lets you compare a string with wildcards - * stands for any number of characters. The InStr function is the VBA function that searches for text in a string.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find chars in SheetName (Excel 2003)

    Hans, you are wonderful !!! You are always there when I need you. This forum is really making me look good at work !
    Thanks,
    --cat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •