Named Range VBA

[IanT]

Hi

I am tryingto use the code below to update the Named Range Excel but it errors, can anyone advise why!

ActiveWorkbook.Names.Add Name:="NOT", RefersToR1C1:= _
"=OFFSET(Extract!$BE$1,0,0,MATCH(" * ",Extract!$BE:BE,-1),1)"

 

[HaHoBe]

Hi, Ian,

the use of CountA instead of Match as well as the use of R1C1 for the ranges may help:

ActiveWorkbook.Names.Add Name:="NOT", RefersToR1C1:= _
    "=OFFSET(Extract!R1C57,0,0,COUNTA(Extract!C57)-1)"

Ciao,
Holger