+ Reply to Thread
Results 1 to 2 of 2

macro to get an array of values from multiple sheets

  1. #1
    Registered User
    Join Date
    09-24-2008
    Location
    PA
    Posts
    13

    macro to get an array of values from multiple sheets

    I have a code but it is not finding the values. Just goes to the msgbox. any suggestions

    sub searchallsheets()
    dim ws as worksheet
    dim rfound as range
    dim sarray as array
    on error resume next
    array = ('1234', '5678', '9101112', '13141516')
    if sarray = "" then exit sub
    for each ws in worksheets
    with ws.usedrange
    sset rfound = .find(what:.=sarray, after:=.cells(1,1), lookin:=xlvalues, lookat:=xlwhole)
    if not rfound is nothing then
    application.goto rfound, true
    exit sub
    end if
    end with
    next ws
    on error goto 0
    msgbox "value not found"
    end sub

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: macro to get an array of values from multiple sheets

    tmiller1,

    In the future, please wrap your code in code tags. See link in my sig for how.
    As for your question, the Find method cannot find more than one value at a time. You'll have to loop through the values in the array and find them individually.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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