Computing Index/Match using data from another worksheet

  • Hello again!


    I was recommended by Carim to start a new thread on this after I posted in an old thread. I am trying to write an an Index Match function that looks across worksheets. Essentially the index match is taking a list of values from sheet 2 and then cross-referencing and pulling values from sheet 3. I am getting a compile error because it expects and end of statement and I might be blind but I can't see what's wrong. This is the piece of code that i'm referring to:




    For some reason I can't attach the workbook. I'll keep trying...thanks again for everything.

  • Re: Computing Index/Match using data from another worksheet


    I'd guess your compile error is connected to the LastRow step.


    Having the period in front of the Cells property suggests that you've defined the worksheet beforehand, i.e.


    Code
    with Worksheets(3)
           LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    end with


    So given the other two are I believe referencing the same worksheet I'd probably go with:


    Code
    with Worksheets(3)
           RR = .Range("a:a") 
           VR = .Range("am:am")
           LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    end with
  • Re: Computing Index/Match using data from another worksheet


    Hi Glovner!


    Thanks for the fast reply. I think I may have not provided enough context. The loop doing the index/match is supposed to run on worksheet 2 (and thus also the lastrow calculation) but the values are being pulled from worksheet 3. I have embedded the full code so that you can see the whole scope of what the macro is doing (beyond this particular problem). You will notice that I have referenced a


    Code
    With Sheet2

    earlier in the sequence to deal with the formatting.



    Thanks again for your help! I'll try posing the workbook ASAP, for some reason i've been having difficulties....

  • Re: Computing Index/Match using data from another worksheet


    Hi Everyone, just wanted to inform that I got around the problem by using a VLOOKUP instead. Thanks for your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!