Array VLOOKUP lookup_Value does not increment


L

L. Howard

Is there a way to make B2 increment as the formula is filled down the columns?
As is, all formulas are identical.

Is it because it's a array formula that prevents the increments?
I increments on the sheet if done manually but not in the code.

Thanks,
Howard

Sub VLook()

Dim LRow As Long

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:F" & LRow).FormulaArray = "=VLOOKUP(B2,Sheet3!$B$2:$F$10,{2,3,4,5},0)"
End Sub
 
Ad

Advertisements

C

Claus Busch

Hi Howard,

Am Wed, 30 Jul 2014 11:12:47 -0700 (PDT) schrieb L. Howard:
Is there a way to make B2 increment as the formula is filled down the columns?
As is, all formulas are identical.

Is it because it's a array formula that prevents the increments?
I increments on the sheet if done manually but not in the code.

try:

Sub VLook()

Dim LRow As Long

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:F2").FormulaArray =
"=VLOOKUP(B2,Sheet3!$B$2:$F$10,{2,3,4,5},0)"
Range("C2:F2").AutoFill Range("C2:F" & LRow)
End Sub


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Wed, 30 Jul 2014 20:19:14 +0200 schrieb Claus Busch:
Sub VLook()

I guess you want:

Sub VLook()

Dim LRow As Long

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:F" & LRow).Formula = _
"=VLOOKUP($B2,Sheet3!$B$2:$F$10,column(B2),0)"

End Sub


Regards
Claus B.
 
Ad

Advertisements

L

L. Howard

I guess you want:



Sub VLook()



Dim LRow As Long



LRow = Cells(Rows.Count, "B").End(xlUp).Row

Range("C2:F" & LRow).Formula = _

"=VLOOKUP($B2,Sheet3!$B$2:$F$10,column(B2),0)"



End Sub





Regards

Claus B.


They both work, I suspect the second one, non array, is a better choice.

Fills down and to the right.

Thanks Claus.
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top