+ Reply to Thread
Results 1 to 6 of 6

Search for a variable and return value of another cell

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    88

    Search for a variable and return value of another cell

    Hi,

    I have attached a sample file to this thread as I think it will be a lot easier for me to explain my problem.

    I have two sheets of data...

    30Day sheet
    The columns I'm interested in are:
    Column D holds USER ID's
    Columns F to AI which contain numbers or blanks, with the headers being dates (oldest to most recent from left to right)

    IDs sheet
    Column B holds USER ID's
    Column D currently blank


    What I want to do is:

    1. On the 'IDs' sheet, look at the first USER ID in Column B
    2. Search for that USER ID in column D of the '30Day' sheet
    3. Once the USER ID has been found on the '30Day' sheet, I'd like to look between columns F:AI for the the most recent non blank cell. Meaning, starting from the RIGHT working backwards to the left, looking for the first non blank cell.
    4. Once this cell is identified (lets say it's column AA), I'd like to capture the value of header row of Column AA
    5. Then place this value into Column D on the 'IDs' sheet against the correct USER ID
    6. Repeat this to the last row on the 'IDs' sheet, searching for the next USER ID in column B

    Note: if the USER ID cannot be found, I'd like "not found" to be placed in Column D on the 'IDs' sheet


    I'm ok at editing code but not the best at starting from scratch. I can create a loop to cycle down the USER ID's and store that as a variable, but I'm not sure how to search for that within the range on sheet '30Day' and then look up the header (row 1) value.

    Any help would be great!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Search for a variable and return value of another cell

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Registered User
    Join Date
    08-16-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    88

    Re: Search for a variable and return value of another cell

    Hi Mick, that's insane, it works perfectly thanks!

    I have to ask, how does it actually work?? I have limited knowledge of VBA but running it through in debug mode, I don't understand what the first loop is doing because it runs that entire loop before moving on to the 'with' statement. If you have a moment could you please explain.

    Thanks

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Search for a variable and return value of another cell

    You're welcome
    This may help your query:-
    The code basically uses a "Dictionary object" ( Google for more info)
    Which stores all the Sheet "30Day", column "D" values as "Keys" and the related Last Date as their "Items". The code then loops though all Column "B" data from Sheet "IDs" ,these values are checked against the "Keys" previously stored from sheet "30Day", in the Dictionary) Those Found "Exists" return the date (Item) the rest return "Not Found"
    Regrds mick

  5. #5
    Registered User
    Join Date
    08-16-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    88

    Re: Search for a variable and return value of another cell

    Thanks again Mick!

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Search for a variable and return value of another cell

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. From Excel, search Access database and return all matches to a variable value
    By bemidjipatriot in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2017, 11:54 AM
  2. [SOLVED] Combine and search each cell in three variable ranges
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2016, 07:07 AM
  3. [SOLVED] VBA variable assign on cell search
    By kamelkid2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2012, 02:23 PM
  4. return multiple similar data on multi variable search
    By patrodgers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-25-2012, 09:38 AM
  5. return multiple similar data on multi variable search
    By patrodgers in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-24-2012, 04:56 PM
  6. Replies: 1
    Last Post: 01-25-2011, 10:50 PM
  7. search outlook contacts in excel and return email address to variable
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-27-2008, 06:08 PM

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