Forum Discussion
Excel VBA Dynamic autofill formula
I tried this and it did not work. It did not copy the formula and autofill the cells going up. This is what I have:
Range("C3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Select
Lrow = Cells(Rows.count, 3).End(xlUp).Row
Range("A3:A" & Lrow).FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Sorry, I wasn't clear about what the code should replace (also, i've now been able to get on a machine and test it and replaced column numbers with letters, in my code - The following replaces the whole section you have shown in your original thread. Let me know if this works for you:
Dim Lrow As Long
Range("C3").Select
'Find last row number with value in column C
Lrow = Cells(Rows.Count, "C").End(xlUp).Row
'Select range A3 to A + Last row number previously found and enter formula
Range("A3:A" & Lrow).FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Range("C3").Select
'Find last row number with value in column B
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
'Select range B3 to B + Last row number previously found and enter formula
Range("B3:B" & Lrow).FormulaR1C1 = "=RIGHT(RC[1], LEN(RC[1])-R1C9-3)"
Range("A1").Select