dynamic named ranges

[peter]

Hi,
I found this on the web
offset(indirect(address(match("H*",sheet1!$a$2:$a$1000,0)
+1,1)),0,0,countif(sheet1!$a$2:$a$1000,"H*"),1)
It works fine when entered as a named range in the ss.
When I try to use it in VBA it errors.

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=OFFSET(INDIRECT(ADDRESS(MATCH(" & "h*"
& ",Sheet1!$A$2:$A$1000,0)+1,1)),0,0,COUNTIF(Sheet1!
$A$2:$A$1000," & "h*" & "),1)"

I hope it has something to do with the quotes not the use
of indirect.
I want to be able to replace the "h*" with a variable.

Any help greatly appreciated.

peter

 

[JE McGimpsey]

Just as in XL, quotation marks within a VBA string must be doubled:

RefersToR1C1:="OFFSET(INDIRECT(ADDRESS(MATCH(""H*"",sheet1!...


To use a variable

Dim sVar As String
sVar = "H*"

RefersToR1C1:="OFFSET(INDIRECT(ADDRESS(MATCH(""" & sVar & _
""",sheet1!...

However, if you're using RefersToR1C1, you have to use R1C1 notation,
not A1-notation, so Sheet1!$A$2:$A$1000 becomes Sheet1!R2C1:R1000C1, or
else change RefersToR1C1 to RefersTo