Please help to to derive a formula

[pol]

I have the following formula

Dim res As Variant
res = ActiveCell.Address
With ActiveSheet
lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Range(res & ":M" & lastrow).FormulaR1C1 = .Range(res).FormulaR1C1
End With

Suppose the value of the res is "M$7". Please help to remove the hardcoding
from the formula
.Range(res & ":M" & lastrow).FormulaR1C1 = .Range(res).FormulaR1C1

I have to remove ":M" , and should be use the fist digit of the res. Please
help

With thanks
Pol

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()
Dim myCell As Range
Dim LastRow As Long

Set myCell = ActiveCell

With ActiveSheet
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
.Range(myCell, .Cells(LastRow, myCell.Column)).FormulaR1C1 _
= myCell.FormulaR1C1
End With

End Sub