excel vba automatic formulas according to my tables sizes


I need to know if the is anyway to automate the range formulas used according to the columns.
This was my original code:

    Sub Formulas_new_sheet()
' Cambiar los rangos acorde al numero de filas usadas

    ' Formula Costo unitario
    ActiveCell.Formula = "=C2/B2"
    'ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Selection.AutoFill Destination:=Range("D2:D7"), Type:=xlFillDefault
    ' Formula de extra / ganancia
    ' Variar segĂșn el porcentaje de ganancia que se busca
     ' recordando .55 = 55% , .30 = 30%
    ActiveCell.Formula = "=D2+(D2*0.55)"
    Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
    'Formula para precio de venta c/u
    ActiveCell.Formula = "=E2+F2"
    Selection.AutoFill Destination:=Range("G2:G7"), Type:=xlFillDefault
End Sub

Then i was help in a forum and i got this:

    Sub Macro3()
TableLastRow = 15
Range("D2:D" & TableLastRow).FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("E2:E" & TableLastRow).FormulaR1C1 = "=RC[-1]+(RC[-1]*0.55)"
Range("G2:G" & TableLastRow).FormulaR1C1 = "=RC[-2]+RC[-1]"
End Sub

But now i want know if is there anyway to automate even more. Not to change TableLastRow every time i do a different table size number of rows


Write Column Formulas

  • Since you have no error handling, one could conclude that column B has always numbers so you could use it to calculate the ‘last row’, the row of the last non-empty cell…

    ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    … or just determine the last non-empty cell…

    ws.Cells(ws.Rows.Count, "B").End(xlUp)

    … as illustrated in the following code:

Sub WriteFormulas()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    With ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
        .Offset(, 2).FormulaR1C1 = "=RC[-1]/RC[-2]"
        .Offset(, 3).FormulaR1C1 = "=RC[-1]+(RC[-1]*0.55)"
        .Offset(, 5).FormulaR1C1 = "=RC[-2]+RC[-1]"
    End With

End Sub

Answered By – VBasic2008

Answer Checked By – Katrina (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.