Excel VBA Dynamic autofill formula

[sequoiacyclist]

I need a little help from the excel gurus out there. I have a spreadsheet where column c has data that changes in the number of cells filled down.  What I need to do is find the last row with data in column c, move over 2 spaces to the left to A and enter a formula and then autofill that formula to A3. Next i need to find the last row with data in column C again and move over one space to the left to column B and enter a formula and then autofill it to B3. 

So far this is what I have:

Range("C3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Selection.AutoFill Destination:=Range("A3:A251"), Type:=xlFillDefault

Range("c3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1], LEN(RC[1])-R1C9-3)"
Selection.AutoFill Destination:=Range("B3:B251"), Type:=xlFillDefault
Range("B3:B251").Select

In the range cells i need A251 and B251 to be dynamic values equal to the number of rows with data in column c. Any ideas?

[#2]

Give this a try:

Dim Lrow As Long
Lrow = Cells(Rows.Count, 3).End(xlUp).Row
Range(“A3:A” & Lrow).FormulaR1C1 = "=LEFT(RC[2], R1C9)"

[#3]

@Charla74 

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)"

[#4]

@sequoiacyclist 

Try this!

Sub test()

Dim Lrow As Integer

Range("C3").Select
Selection.End(xlDown).Select

Lrow = Selection.Row

Range("A3").FormulaR1C1 = "helloA"

Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Lrow), Type:=xlFillDefault

Range("B3").FormulaR1C1 = "helloB"

Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B22")

End Sub

Just replace the "helloA" and "helloB" with your formulae!

[#5]

@sequoiacyclist 

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

[#6]

@sequoiacyclist

....apologies! I had to make a quick edit due that your formulae are based on a starting location of cell C3.

Hopefully this works.