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.

 

[RoryA]

What error and what code? My guess would be a lack of specified worksheet.

 

[Johnny C]

Hi Rory
it's error 438 (object doesn't support this method or property.

This works:
dblLOBIndex = WorksheetFunction.Match(str_LOB, wksLookUps.Range("R2:R65536"), 0)
wksLookups is a worksheet in the file running the VBA, where the wksLookUpsname is set in the Property box in VBA

This doesn't:
dblLOBIndex = WorksheetFunction.Match(str_LOB, wksLookUps.Range("List_LOB"), 0)
where list_LOB is a dynamic range
=OFFSET(LookUps!$R$1,1,0,COUNTA(LookUps!$R$2:$R$50000),1)

This doesn't, but is what i'm trying to do:
dblLOBIndex = WorksheetFunction.Match(str_LOB, wkbkOpened.wksdatasheet.Range("List_LOB"), 0)
wkbkOpened is assigned a workbook with a SET, wksdatasheet is set to each worksheet in a loop that cycles through everything in the workbook.

Cheers!
Johnny

 

[RoryA]

I cannot see how you could get a 438 error on that line.
Your third option won't work since:
1. The worksheet variable already refers to a specific worksheet - it is not a member of the workbook.
2. The range belongs to a specific worksheet so that is the only sheet you can qualify it with.

 

[Johnny C]

Ok, it works with just the range name.

However, the same range name is on the workbook with the VBA in that opens the other workbook and the other workbook.

How can I phrase the match so that it uses the version of the rangename on the 'other' workbook, is there a way without activating the workbooks?

The reason I need to do that is that the calling workbook with the VBA creates the sheets in the other workbook if they doesn't exist, and need the rangename for data validation; the VBA needs to check the version of the data on the 'other workbook' as it can have a more updated list on it's own version of the list that would cause the match not to work on the 'other' workbook, and needs copying in for the following month.
Other workbook has last month's data that the VBA needs to check against,
Calling workbook has newer data that can supercede the data for next month
Calling workbook needs the same dynamic range name as it's used for data validation on sheets which it can copy over if the 'Other workbook' doesn't have them.

Sorry not explaining it well, it's complicated!

 

[RoryA]

You specify the sheet that the range belongs to, so I don't really understand the problem. The range name will always refer to the correct workbook.

 

[Johnny C]

I tried wksdatasheet.Range("List_LOB"), and that doesn't work.

wksdatasheet is a sheet object, and it has that range defined for it. Do I need to be specific and use sheets(wksdatasheet.name) instead?

 

[RoryA]

No, that should work if the range is on that sheet. (note: I don't mean if the name is defined as local to that sheet - I mean if the range is actually located on that sheet).

 

[Johnny C]

Umm, tried using sheets(wksdatasheet.Name).range(str_LOB) and that didn't work either.

Oh well, have to use Activates if I need to swap sheets, I was hoping to avoid that as it slows it down with some of the larger sheets (30Krows+) but I'll have to live with it. Or not do that check if it slows things down too much.

Thanks anyway!

 

[RoryA]

You do NOT have to activate sheets to use named ranges.