Hi,
I am trying to autofill vlookup across row, till the last column.
My excel have few sheets. Each sheet, number of columns is not fixed.
First row has codes. I am getting descriptions for these codes from another sheet into current sheet.
I insert a row below to first row, then trying to autofill across the row, based on the previous row(above cell). If above cell is empty, dont apply formula.
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>
The code I write looks like this
Range("A1").Select
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
Set rng = ActiveSheet.Range("A2")
FinalResult = Application.WorksheetFunction.VLookup(Range("A1"), Sheet1.Range("A:B"), 2, False)
rng = FinalResult
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:I2")
I want it to be dynamic based on previous row cell or column count .. instead of A2:I2
Can some one help me how to fix it and make general ?
Thanks in advance.
I am trying to autofill vlookup across row, till the last column.
My excel have few sheets. Each sheet, number of columns is not fixed.
First row has codes. I am getting descriptions for these codes from another sheet into current sheet.
I insert a row below to first row, then trying to autofill across the row, based on the previous row(above cell). If above cell is empty, dont apply formula.
D1 | D2 | D3 | D4 | D5 | D6 | D7 | ||
Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | Tuesday |
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>
The code I write looks like this
Range("A1").Select
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
Set rng = ActiveSheet.Range("A2")
FinalResult = Application.WorksheetFunction.VLookup(Range("A1"), Sheet1.Range("A:B"), 2, False)
rng = FinalResult
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:I2")
I want it to be dynamic based on previous row cell or column count .. instead of A2:I2
Can some one help me how to fix it and make general ?
Thanks in advance.