Try:
[VBA]Sub example()
Const CounterA As Long = 1
Const ColumnA As Long = 1

Cells(10, 10).Formula = "=" & Cells(CounterA, ColumnA).Address(0, 0, , -1)
End Sub[/VBA]