Using dynamic range names

[Johnny C]

I'm trying to do a worksheetfunction.match on a range.

If I use a dynamic range name as the search array, it goes me an error. If I do it with a hard-coded range, say "V1:V50000" it works. Any idea why?

The dynamic range name is used by data validation, and that's working fine, there's no problem with the dynamic range itself.

 

[Johnny C]

Ah, I see now.

I assumed that if I know the name of the sheet which has the rangeneame defined for it then it would work, even if I don't know the name of the sheet within the dynamic range formula definition.

Unfortunately, it's not simple. The sheet that the range refers to (LookUps) gets monkeyed around with, people change the name (goodness knows why) then rename it back, and the capitalisation gets put back wrong. So it could be lookups, lookups, LOOKUPS, LookUps... even worse when the call-centre (foreign) get hold of the files. So I can't predict what the name will be, all I know is that the rangename will have the correct version encoded in. I suppose I could parse the formula in the range name defintion and extract the sheetname.

 

[RoryA]

Is the name defined at worksheet level or workbook level?

 

[Johnny C]

Worksheet level. I can change it to workbnook level though, as the macro redefines the names each time it runs.

 

[RoryA]

No, it's OK, I just needed to know how to refer to the name:

wksdatasheet.Names("List_LOB").RefersToRange

should give the range you want.

 

[Johnny C]

yes,

ActiveWorkbook.Worksheets("Query Log").Names.Add Name:="List_LOB", RefersToR1C1:="=OFFSET(LookUps!R1C18,1,0,COUNTA(LookUps!R2C18:R50000C18),1)"

for worksheet level,

ActiveWorkbook.Names.Add Name:="List_LOB", RefersToR1C1:="=OFFSET(LookUps!R1C18,1,0,COUNTA(LookUps!R2C18:R50000C18),1)"

for workbook.

 

[RoryA]

Not sure what that is in response to?

 

[Johnny C]

Your previous thread about defining the names. Sorry, I was confused myself, it was more of a comment and I was changing the code as you posted to create the ranges to workbook-scope.

 

[RoryA]

OK, well if you make them workbook-level, then you refer to the name as a property of the workbook rather than sheet, but RefersToRange should still do the job.

 

[Johnny C]

Mucho Gracias. I'll worry about it being pretty later, but at least I can get it working now.