+ Reply to Thread
Results 1 to 7 of 7

Problem searching multiple worksheets via VBA

  1. #1
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100

    Problem searching multiple worksheets via VBA

    Hi,

    What I'm trying to do is take a value from one workbook, and search for it in a different workbook which has data spread over 12 worksheets. When it finds the data, I need it to pass the name of the worksheet back to the original workbook. I used macro recorder to get the code for selecting and searching the multiple worksheets, but when I added that into my existing code, I got an error (With block not set?)

    Here's what I'm using - can anyone figure out why it won't work?

    Please Login or Register  to view this content.
    Last edited by madbloke; 02-06-2009 at 06:12 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Problem searching multiple worksheets via VBA

    Don't declare all your variables as variants.

    Try this

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100

    Re: Problem searching multiple worksheets via VBA

    Cheers for that, Roy - I'm getting an error, though - I'm getting 'Runtime error 438 - object doesn't support this property or method' for this bit:
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Problem searching multiple worksheets via VBA

    You are missing the reference to the worksheet object.

    Current the With is in reference to the Workbook

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100

    Re: Problem searching multiple worksheets via VBA

    That's cracked it, cheers, Andy!

    Can I be cheeky and add another quick question? The If Not cl is Nothing bit is great, but is there a way to add a bit underneath for if cl IS nothing? I.e if the data isn't found on the sheet. I thought the attached would work, but it's saying I've got an else without an if.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Problem searching multiple worksheets via VBA

    Yes you just need to change the syntax slightly.
    Changing the "In Line" If to a Block If statement.

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100

    Re: Problem searching multiple worksheets via VBA

    Cheers for the swift response, Andy, but it turns out I'm just being silly. First macro works fine - if it doesn't find the data, it leaves a blank, which is fine.

    You and Roy have saved me about two hours a day! Thanks loads! I love this forum!!
    Last edited by madbloke; 02-06-2009 at 06:06 AM.

+ 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