Vlookup in VBA (how to use a dynamic range)

[Dennis]

Using XL 2003 & 97

Attempting, with VBA, to autofill a column range with a vlookup formula.

The vlookup formula that works fine (if I manually select the range) is:

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R274C7,1,FALSE)"

The challenge is that the table range of R4C6:R274C7 was the result of
manual selection.

I attempted to use VBA to automatically determine the range:

Dim myRange As Range
Set myRange = Range("F3:G" & Range("F" & Rows.Count).End(xlUp).Row)

What is the syntax to use myRange in the following VBA code? (Or where am I
off base?)

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1], ???????? ,1,FALSE)"

Once I know how to incorporate myRange, then I will use the following to
filldown:

Range("B4").AutoFill Destination:=Range("B4:B" & Range("A" &
Rows.Count).End(xlUp).Row), Type:=xlFillCopy

TIA

Dennis

 

[Bob Phillips]

Dennis,

You don't need a range, just plug the row number in the formula

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R" & _
Range("F" &
Rows.Count).End(xlUp).Row & _
"C7,1,FALSE)"

or

cRow = Range("F" & Rows.Count).End(xlUp).Row

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R" &cRow & _
"C7,1,FALSE)"



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dennis]

Thanks Bob!

Dennis,

You don't need a range, just plug the row number in the formula

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R" & _
Range("F" &
Rows.Count).End(xlUp).Row & _
"C7,1,FALSE)"

or

cRow = Range("F" & Rows.Count).End(xlUp).Row

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R" &cRow & _
"C7,1,FALSE)"



--

HTH

RP
(remove nothere from the email address if mailing direct)

Using XL 2003 & 97

Attempting, with VBA, to autofill a column range with a vlookup formula.

The vlookup formula that works fine (if I manually select the range) is:

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1],R4C6:R274C7,1,FALSE)"

The challenge is that the table range of R4C6:R274C7 was the result of
manual selection.

I attempted to use VBA to automatically determine the range:

Dim myRange As Range
Set myRange = Range("F3:G" & Range("F" & Rows.Count).End(xlUp).Row)

What is the syntax to use myRange in the following VBA code? (Or where am I
off base?)

Range("B4").FormulaR1C1 = "=VLOOKUP(RC[-1], ???????? ,1,FALSE)"

Once I know how to incorporate myRange, then I will use the following to
filldown:

Range("B4").AutoFill Destination:=Range("B4:B" & Range("A" &
Rows.Count).End(xlUp).Row), Type:=xlFillCopy

TIA

Dennis