+ Reply to Thread
Results 1 to 4 of 4

Find Worksheet with Wildcard

  1. #1
    Registered User
    Join Date
    08-14-2005
    Posts
    13

    Find Worksheet with Wildcard

    Hi guys,

    I like to know how to find a worksheet in a workbook in which I know the name begins with the word "Completed". The trouble is I am not too sure how you incorporate wildcard with the search as well, e.g. the sheet could be called "Completed 0506"

    After the search i would like to set that worksheet to a worksheet variable as well, so i can acesss that worksheet for further data access.

    Many thanks

  2. #2
    Dave Peterson
    Guest

    Re: Find Worksheet with Wildcard

    dim wks as worksheet
    dim CompWks as worksheet

    set CompWks = nothing
    for each wks in activeworkbook.worksheets
    if lcase(wks.name) like "completed *" then
    set compwks = wks
    exit for
    end if
    next wks

    if compwks is nothing then
    'not found
    'what should happen
    else
    'found it!
    end if

    philwongnz wrote:
    >
    > Hi guys,
    >
    > I like to know how to find a worksheet in a workbook in which I know
    > the name begins with the word "Completed". The trouble is I am not too
    > sure how you incorporate wildcard with the search as well, e.g. the
    > sheet could be called "Completed 0506"
    >
    > After the search i would like to set that worksheet to a worksheet
    > variable as well, so i can acesss that worksheet for further data
    > access.
    >
    > Many thanks
    >
    > --
    > philwongnz
    > ------------------------------------------------------------------------
    > philwongnz's Profile: http://www.excelforum.com/member.php...o&userid=26283
    > View this thread: http://www.excelforum.com/showthread...hreadid=565508


    --

    Dave Peterson

  3. #3
    Registered User
    Join Date
    08-14-2005
    Posts
    13
    Many thanks Dave,

    I was lucky enough to came up with a solution just before i recheck anyone has replied, so I can post the solution to my question. Here's what I've done, which is almost the same as your solution, except the helper methods returns a worksheet object rather than a boolean. As I need to do several searches on different workbooks I have used a workbook as a criteria for this function, rather than activiting the workbook prior to my search.

    Public Function SheetExists(currentWorkbook As Workbook, strSearchFor As String) As Worksheet
    Dim tempWks As Worksheet

    For Each tempWks In currentWorkbook.Worksheets
    If tempWks.Name Like strSearchFor Then
    Set SheetExists = tempWks
    Exit Function
    Else
    Set SheetExists = Nothing
    End If
    Next tempWks
    End Function

  4. #4
    Dave Peterson
    Guest

    Re: Find Worksheet with Wildcard

    My suggestion actually used a worksheet variable -- compwks.

    You may want to make sure you have:
    option compare text
    at the top of the module

    Or make sure you pass the correct case (or use ucase/lcase) in your function.

    philwongnz wrote:
    >
    > Many thanks Dave,
    >
    > I was lucky enough to came up with a solution just before i recheck
    > anyone has replied, so I can post the solution to my question. Here's
    > what I've done, which is almost the same as your solution, except the
    > helper methods returns a worksheet object rather than a boolean. As I
    > need to do several searches on different workbooks I have used a
    > workbook as a criteria for this function, rather than activiting the
    > workbook prior to my search.
    >
    > Public Function SheetExists(currentWorkbook As Workbook, strSearchFor
    > As String) As Worksheet
    > Dim tempWks As Worksheet
    >
    > For Each tempWks In currentWorkbook.Worksheets
    > If tempWks.Name Like strSearchFor Then
    > Set SheetExists = tempWks
    > Exit Function
    > Else
    > Set SheetExists = Nothing
    > End If
    > Next tempWks
    > End Function
    >
    > --
    > philwongnz
    > ------------------------------------------------------------------------
    > philwongnz's Profile: http://www.excelforum.com/member.php...o&userid=26283
    > View this thread: http://www.excelforum.com/showthread...hreadid=565508


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1