Please help to to derive a formula
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 |
Please help to to derive a formula
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 pol wrote: 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 |
All times are GMT +1. The time now is 01:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com