Formula Auto Fill VBA

Question

I managed to get this to work, but the problem is I have to specify the range (in this case I just hard coded C2:c25 and the file will have different row counts every time.

Is there a way to make this run only for the rows that have data?

Sub addFormulas()

ThisWorkbook.Worksheets("Sheet2").Range("C2").Formula = "=(B2/12)*100"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C25")
End Sub

Do you mean you need to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba)? — BigBen, Oct 02 '20 at 01:23
Yeah, basically to only run the formula up until the last row (which will be a variable amount of rows i.e. different every time) — Amateurhour35, Oct 02 '20 at 01:27
Did you click the link in my comment? That's the approach to take. — BigBen, Oct 02 '20 at 01:50

Ivan · Answer 1 · 2020-10-02T02:26:46.073

You could use xlDown to find your last row number based on column B, so you won't have to change your code next time.

Sub addFormulas()
    ThisWorkbook.Worksheets("Sheet2").Range("C2").Formula = "=(B2/12)*100"
    Range("C2").Select
    'find last row with value
    last_row = Range("B2").End(xlDown).Row
    Selection.AutoFill Destination:=Range("C2:C" & last_row)
End Sub

But I'm assuming that column B doesn't have blank cells between values. In case column B may have blank cells, you could run a FOR loop to find the last row. It's a lot less efficient, but it works well as long as you don't have a very large number of rows:

Sub addFormulas2()
    ThisWorkbook.Worksheets("Sheet2").Range("C2").Formula = "=(B2/12)*100"
    Range("C2").Select
    For i = 1 To 20
        If Range("B" & i) & "" > "" Then last_row = i
    Next i
    Selection.AutoFill Destination:=Range("C2:C" & last_row)
End Sub

EDIT: Just learned here that using xlUp ir more efficient than FOR and more reliable than xlDown, since it won't have any problems if there's some blank cell in column B:

Sub addFormulas_()
    ThisWorkbook.Worksheets("Sheet2").Range("C2").Formula = "=(B2/12)*100"
    Range("C2").Select
    With Sheets("Sheet2")
        last_row = .Range("B" & .Rows.Count).End(xlUp).Row
    End With
    Selection.AutoFill Destination:=Range("C2:C" & last_row)
End Sub

It's better to use `xlUp` to find the last row as demonstrated in the link in the first comment. No need to loop either. — BigBen, Oct 02 '20 at 02:04
Thanks, I didn't know that approach, I just knew xlDown wasn't reliable when there's blank cells, and since most of the time I don't have too many rows, I just used FOR loops. — Ivan, Oct 02 '20 at 02:11

score 0 · Answer 2 · answered Oct 02 '20 at 05:13

You can accomplish your task without using AutoFill or Select. It is better to use a With … End With statement. Comments are provide in the code below.

Sub addFormulas()
    With ThisWorkbook.Worksheets("Sheet2") 'used to set the focus on the worksheet object
    
        .Cells(2, 3).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1).Formula = "=(B2/12)*100"
            'writes the formula in the variable range in column C    
            'Breakdown...
                'the "." (dot) e.g. (.Cells and .Rows) is used to refer to the worksheet object in the With statement
                '".Cells(2, 3)" is the start of the range you want to write the formula
                '".Resize(.Cells(.Rows.Count, 2).End(xlUp).Row")expands the range to the last use row in column B
                'the "- 1" adjusts your range because you started on row 2
    End With
End Sub

Formula Auto Fill VBA

2 Answers2