Excel VBA - Problems with Quotation Marks in Range Formula 1

[jmarkus]

Hi,

I'm using the following code to create a series of named range formulae:

    ImageRanges(TableSz) = "INDEX(PARTS!$E$5:$E$200,MATCH(BOM!$D$" & ImgRowNum & ",PARTS!$B$5:$B$200,0))"
    RangeNames(NItems) = "AsmImage" & NItems
    ActiveWorkbook.Names.Add Name:=RangeNames(NItems), RefersTo:=ImageRanges(TableSz)

The problem is that the result of my concatenated string above contains starting and ending quotations. So instead of

=INDEX(PARTS!$E$5:$E$200,MATCH(BOM!$D$5,PARTS!$B$5:$B$200,0))

in the Name Manager, I see

="INDEX(PARTS!$E$5:$E$200,MATCH(BOM!$D$5,PARTS!$B$5:$B$200,0))"

And Excel doesn't like that...

How do I get it to strip the quotes from the range?

Thanks,
Jeff

 

[SkipVought]

Hi,

[tt]
Dim xl As Application
Set xl = Application
ImageRanges(TableSz) = xl.INDEX(Sheets("PARTS").Range("$E$5:$E$200"),xl.MATCH(Sheets("BOM").Range("$D$" & ImgRowNum),Sheets("PARTS").Range("$B$5:$B$200"),0))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

You need "=" in formula, i.e.:
[tt]ImageRanges(TableSz) = "=INDEX(PARTS!$E$5:$E$200,...[/tt]

combo

 

[jmarkus]

Thanks combo!

It was the missing "=" that messed me up.

Jeff