Names.Add - compile error

[Eric @ BP-EVV]

Can anyone tell me what's wrong with the following statement that is
preventing my VBA code from compiling ?

Names.Add(Name:="Base",
RefersTo:=ActiveCell.Offset(WorksheetFunction.CountA("$a$2:$a$60000"),6 as
range)

Thank !

 

[Alex Simmons]

Can anyone tell me what's wrong with the following statement that is
preventing my VBA code from compiling ?

Names.Add(Name:="Base",
RefersTo:=ActiveCell.Offset(WorksheetFunction.CountA("$a$2:$a$60000"),6 as
range)

Thank !

You shouldn't need the "As Range". This worked for me:

Names.Add "Base", ActiveCell.Offset(WorksheetFunction.CountA("$A$2:$A
$60000"),6)

Alex

 

[Don Guillett]

try this.

Sub makename()
ma = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="base", RefersTo:= _
"=OFFSET(" & ma & ",COUNTA($a$2:$a$6000),6)"
End Sub