VBA, Match, Worksheet_Change and named ranges

[cornflakegirl]

Two related questions in one.

I have this line of VBA:

If Not IsNumeric(Application.Match(sAgg, Sheet6.Range(sRange), 0)) Then Range("Product").Value = "All"

sAgg and sRange are strings. sRange contains the name of a named range.

I originally had Application.WorksheetFunction.Match, but had to take WorksheetFunction out because the Match evaluates to error - is that the proper way to deal with that?

Also, this code is in a Worksheet_Change routine. My named range Range(sRange) is on a different worksheet. I originally tried to use just Range(sRange) but it errored until I defined which sheet it was on. Is there a way that I can use the named range without specifying the sheet? (I tried ActiveWorkbook.Range(sRange) but that didn't work either.)

 

[VoG]

Hi.

Yes Application.Match is the right way to go.

If it is a worksheet specific defined name then you have to qualify it with the sheet name, index or codename.

 

[MickG]

This might Help:-

Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveWorkbook.Names("sRange").RefersToRange.Worksheet
MsgBox ws.Name
Set rng = ActiveWorkbook.Names("sRange").RefersToRange
MsgBox rng.Address
If Not IsNumeric(Application.Match(sAgg, rng, 0)) Then Range("Product").value = "All"

Mick

 

[cornflakegirl]

@VoG - it's not a worksheet specific name. I'm using it in the actual worksheet that the code belongs to without a problem, and there aren't any other named ranges with the same name.

@Mick - thanks for that. Will go and look up what RefersToRange does.

 

[cornflakegirl]

RefersToRange is just what I need.

For the benefit of anyone else - I found this thread where pgc explains the problem a bit further, and suggests the same solution.

Thanks both very much.