Dynamic Range VBA Index Multi Match

[Noobtastic]

Hello,

I have the following line that works fine but would like to have dynamic range for the array and lookup values.

Cells(C,32).value = "=INDEX(RateSheet!C2:I5,MATCH(C2,RateSheet!A2:A5,1),MATCH(AE2,RateSheet!C1:I1,1))"

C2:I5 and A2:A5 could have additional rows
C1:I1 could have additional columns
Both C2 and AE2 would lookup until last row

Would anyone be able to assist?

 

[Noobtastic]

Just an update, I was able to make the lookup values dynamic with the code below.

Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
For C = 2 To LastRow

Cells(C, 32).Value = "=INDEX(RateSheet!C2:I5,MATCH(C" & C & ",RateSheet!A2:A5,1),MATCH(AE" & C & ",RateSheet!C1:I1,1))"



Now I'm just trying to figure out making the arrays dynamic. For example with C2:I5, would it be something to this degree?

Dim lsatrow2, lastcol2 as Long

lastRow2 = Range("A" & Rows.Count).End(xlUp).Row
LastCol2 = Cells(1, Columns.Count).End(xlToLeft).Column

Cells(C, 32).Value = "=INDEX(RateSheet!" & Range(Cells(2,3),Cells(lastrow2,lastcol2)) & ",MATCH(C" & C & ",RateSheet!A2:A5,1),MATCH(AE" & C & ",RateSheet!C1:I1,1))"

 

[Noobtastic]

Hello all, I was able to work around dynamic last row with the following code below.

Cells(C, 32).Value = "=INDEX(RateSheet!C2:I" & LastRow1 & ",MATCH(C" & C & ",RateSheet!A2:A" & LastRow1 & ",1),MATCH(AE" & C & ",RateSheet!C1:I1,1))"

I tried to do the same for last column but it returns a 9 for I, and the line would read C1:91 instead of C1:I1. My question now becomes if there's a way to read the 9 as I?