Computing Index/Match using data from another worksheet

b0t · Aug 28th 2017

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:

Code

Dim LastRow As Integer
Dim RR As Variant
Dim VR As Variant




RR = Worksheets(3).Range("a:a")
VR = Worksheets(3).Range("am:am")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row








For x = 25 To LastRow




cells (x,"ao") = "=INDEX(VR,MATCH(cells(x,"a"),RR,0))"




Next x

Display More

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

TheGlovner · Aug 28th 2017

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

b0t · Aug 30th 2017

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.

Code

Sub test()




Sheets("query").Select
Sheets("query").Copy before:=Worksheets(2)




With Sheet2




'add columns
Cells(24, "an") = "In top conso prior month"
Cells(24, "ao") = "CTD Delta Margin in Top Conso Prior Month"
Cells(24, "ap") = "DT 35 in previous period"
Cells(24, "aq") = "CTD + DT35 previous period"
Cells(24, "ar") = "Delta"
Cells(24, "as") = "Adj Needed"












'add filters




Worksheets(2).AutoFilterMode = False
Rows("24:24").Select
Selection.AutoFilter
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=7, Criteria1:="="
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=3, Criteria1:= _
"Result"
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=39, Criteria1:="<-0.01", Operator:=xlOr, Criteria2:=">0.01"




'calculate columns




Dim LastRow As Integer
Dim RR As Variant
Dim VR As Variant




RR = Worksheets(3).Range("a:a")
VR = Worksheets(3).Range("am:am")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row








For x = 25 To LastRow




cells (x,"ao") = "=INDEX(VR,MATCH(cells(x,"a"),RR,0))"




Next x








End With








End Sub

Display More

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

b0t · Sep 12th 2017

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!

Computing Index/Match using data from another worksheet

Participate now!

Share