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

A1 Style References

   <source lang="vb">

Sub m()

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

End Sub

</source>
   
  

Entering A1 Versus R1C1 in VBA

   <source lang="vb">

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

</source>
   
  

R1C1 Style

   <source lang="vb">

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

</source>
   
  

R1C1 Style References

   <source lang="vb">

Sub r1c1()

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

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Use loop to select cells

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>