VBA/Excel/Access/Word/Excel/Cell Reference

A1 Style References

 
Sub m()
    Range("D5").Value = "=SUM(D1:D4)"
    Range("D5").Formula = "=SUM(D1:D4)"
End Sub

Entering A1 Versus R1C1 in VBA

 
Sub A1Style()
    FinalRow = cells(Rows.count, 2).End(xlUp).row
    range("D4").Formula = "=B4*C4"
    range("F4").Formula = "=IF(E4,ROUND(D4*$B$1,2),0)"
    range("G4").Formula = "=F4+D4"
    range("D4").copy Destination:=range("D5:D" & FinalRow)
    range("F4:G4").copy Destination:=range("F5:G" & FinalRow)
    cells(FinalRow + 1, 1).value = "Total"
    cells(FinalRow + 1, 6).Formula = "=SUM(G4:G" & FinalRow & ")"
End Sub

R1C1 Style

 
Sub R1C1Style()
    FinalRow = cells(Rows.count, 2).End(xlUp).row
    range("D4:D" & FinalRow).FormulaR1C1 = "=RC[-1]*RC[-2]"
    range("F4:F" & FinalRow).FormulaR1C1 = "=IF(RC[-1],ROUND(RC[-2]*R1C2,2),0)"
    range("G4:G" & FinalRow).FormulaR1C1 = "=+RC[-1]+RC[-3]"
    cells(FinalRow + 1, 1).value = "Total"
    cells(FinalRow + 1, 6).Formula = "=SUM(G4:G" & FinalRow & ")"
End Sub

R1C1 Style References

 
Sub r1c1()
    Dim formulaString As String
    formulaString = "=R[0]C[-1]+ R[-1]C[0]"
    Cells(2, "B").FormulaR1C1 = formulaString
End Sub

The Cells property is best suited for use with loops because it accepts numerical parameters representing the row and column indices in a worksheet

 
Sub cellPro()
    Dim I As Integer
    Dim K As Integer
    For K = 1 To 5
        For I = 1 To 10
            Cells(I, K).Value = I * K
        Next I
    Next K
End Sub

Use loop to select cells

 
Sub forDemo()
    Dim i As Integer
    For i = 1 To 24
        ActiveCell.FormulaR1C1 = i & ":00"
        ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Select
    Next i
End Sub

Write a formula in the first 10 columns of row 21 in a worksheet that calculates the standard deviation of the values in rows 2 through 20

 
Sub formulaDemo()
    Dim I As Integer
    For I = 1 To 10
           Cells(21, I).Formula = "=STDEV(" & Chr(I + 64) & "2:" & Chr(I + 64) & "20)"
    Next I
End Sub