Array VLOOKUP lookup_Value does not increment
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 |
Array VLOOKUP lookup_Value does not increment
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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Array VLOOKUP lookup_Value does not increment
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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Array VLOOKUP lookup_Value does not increment
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 |
All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com