PDA

View Full Version : Solved: Resize with Vlookup Help



jmarkc
09-15-2009, 09:41 AM
I've been trying to figure this out on my own with no luck. Searched the forum here (and elsewhere) and tried various code - no luck.

I have a workbook that uses VLOOKUP to find a match between worksheets. That works fine. The problem I have is when I try to autofill the formula through the whole column it changes my Table Array. Based on what I've seen, I believe I need to use Resize to maintain the same Table Array. The Table Array for the VLOOKUP should always be "A2:LastRow". Can someone help me solve this?

Range("o4:o4").FormulaR1C1 = "=VLOOKUP(RC[-14],Format!R[-2]C[-14]:R[799]C[-5],8,TRUE)"

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("o4").AutoFill .Range("o4").Resize(lastrow)
End With

I can attach a sample workbook if necessary.

Thanks in advance for the help.

Bob Phillips
09-15-2009, 09:55 AM
Range("o4:o4").FormulaR1C1 = "=VLOOKUP(RC[-14],Format!R2C1:R703C10,8,TRUE)"

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("o4").AutoFill .Range("o4").Resize(lastrow)
End With

jmarkc
09-15-2009, 10:02 AM
Much appreciated! Worked perfectly. Focused on the wrong area. Didn't even consider the formula itself.

Thanks again!