macro does not like $

[ndendrinos]

This code (restores named ranges in case they get in trouble) errors like this

Compile error/Invalid character,syntax error

& the $ sign of $A$2,0,0 is highlighted

Sub RestoreNamedRanges()
Dim Rng1 As Range     
Set Rng1 =OFFSET(Customers!$A$2,0,0,COUNTA(Customers!$A:$A),1)
ActiveWorkbook.Names.Add Name:="Customers", RefersTo:=Rng1 
End Sub

What am I doing wrong again?

 

[xenou]

You want a string not a range here (being the difference betwee a formula that returns a range, and the range it refers to).

Sub RestoreNamedRanges()
Dim sFormula As String     
sFormula = "=OFFSET(Customers!$A$2,0,0,COUNTA(Customers!$A:$A),1)"
ActiveWorkbook.Names.Add Name:="Customers", RefersTo:=sFormula 
End Sub

I think you could evaluate the formula to get the range reference itself, but this would not be a dynamic range any more, but a hard coded one:

Sub RestoreNamedRanges()
Dim rng1 As Range     
Set rng1 = WorksheetFunction.OFFSET(Range("Customers!$A$2"),0,0,WorksheetFunction.COUNTA(Range("Customers!$A:$A")),1)
rng1.Name = "Customers"
End Sub

Not tested in either case ... Hope it works.
ξ

 

[ndendrinos]

Thank you xenou, first code works fine