In my workbook, I have many rows in different sheets. In the first cell of each row(column A), there is a name (header) and in the other cells there are some numbers relating to first row (for example, A1 is inflation and A2:A10 are .01,.02 …which are inflation amount of different years). For making my formula more readable, I want to use dynamic relative named ranges. For automating this process, I have written following macro. There are 2 problem with this macro:
1.VBA consider parameters of my macro as text not value (for example, VBA considers cellAddress02 as “cellAddress02” not $A$1 which is what I expect)
2.For making my named ranges relative rather than absolute, I need a VBA function to give a cell address which doesn't have $ signs before the letter (for example, I want A$1 not $A$1)
Can anyone help me on these problems?


Sub Macro3()
'Dim rng As Range
Dim n As Integer
Dim myrow As Integer
Dim cell As Range
Dim cell02 As Range
Dim cellAddress As String
Dim cellAddress02 As String
myname = ActiveCell.Value
myrow = ActiveCell.Row
Set cell = ActiveCell
cellAddress = cell.Parent.Name & "!" & cell.address(External:=False)
Set cell02 = Range("A1")
cellAddress02 = cell02.Parent.Name & "!" & cell02.address(External:=False)
ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersToR1C1:= _
"=INDEX(cellAddress02:cellAddress,myrow)"
End Sub