Forum Discussion

sequoiacyclist's avatar
sequoiacyclist
Copper Contributor
Feb 04, 2020

Excel VBA Dynamic autofill formula

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?

excel
Macros and VBA

5 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Feb 04, 2020
    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)"
    • sequoiacyclist's avatar
      sequoiacyclist
      Copper Contributor
      Feb 04, 2020

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

       

       

      • Charla74's avatar
        Charla74
        Iron Contributor
        Feb 04, 2020

        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

Resources