Solved: Resize with Vlookup Help

[jmarkc]

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?

[vba] 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[/vba]

I can attach a sample workbook if necessary.

Thanks in advance for the help.

[Bob Phillips]

[vba]

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
[/vba]

[jmarkc]

Much appreciated! Worked perfectly. Focused on the wrong area. Didn't even consider the formula itself.

Thanks again!