Return unlocked .address in VBA

[dimitri]

Hi,
the piece if code below ultimately puts the formula "=$B$2" in cell A1 (SS.Name is the name of cell B2).

Dim r As String
r = Range("SS.Name").Address
Range(A1).Formula = "=" & r

I'm looking for a way for the code to return a value that is not locked, meaning "=B2" instead of "=$B$2". The reason is that later I want to drag this formula and don't want cell B2 to be locked.

Any help would be appreciated. Thank you.

 

[mikerickson]

With Range("SS.Name")
    Range("A1").FormulaR1C1 = .Address(false, false, xlR1C1,,.Cells(1,1))
End With

 

[VoG]

Try

Dim r As String
r = Range("SS.Name").Address(False, False)
Range("A1").Formula = "=" & r

 

[dimitri]

Those are great. Thank you for the help!