What am I doin wrong with my set range = code?

[miless2111s]

Whilst running some test code to work out which is quicker between vlookups and match/offset (see my other thread) I found that I'd caused some sort of odd effect... Within my code I often set up a reference to a named range so that I can use this (for instance in a vlookup) and this has previously worked fine. However I was then finding that my normal method was apparently randomly failing to actually set the range up and so the calculation was failing.

What I normally do:

Dim MS_Data As Range
Set MS_Data = Range("MS_Data")
Start = Application.WorksheetFunction.VLookup(Vref, MS_Data, 2, False)

This was apparently failing to store or find the MS_Data range and so was causing an error or returning a blank as it didn't know what I was asking it to look in.

However another range, called in the way was working!

I solved this by activating the sheet where the range is located:

Dim MS_Data As Range
Worksheets("MS Project Bars Import").Activate
Set MS_Data = Range("MS_Data")
Start = Application.WorksheetFunction.VLookup(Vref, MS_Data, 2, False)

as I am such a beginner at this I wonder if I have broken some sort of rule or had I just got lucky all the other times?

Many thanks for any guidance or suggestions as I really can't cope with the idea that the code can appear to behave randomly as I know it can't and it must be user error!

Miles

 

[Sektor]

It seems that your named range has sheet scope rather workbook scope. You can see the scope in Name Manager.

 

[Leith Ross]

Hello miless2111s,

Named Range references can be restricted to either a worksheet or can be can accessed from anywhere (the workbook).
It appears when your named range "MS_Data" was created, it's reference was only to the worksheet "MS Project Bars Import". That is why when you activated the sheet it worked.

 

[miless2111s]

Thanks for your quick replies. Looking at the Name Manager all my ranges are Scope = Workbook and I haven't had this trouble before with these ranges (they're used in a much bigger code set and don't appear to have this issue but now I'm worried that maybe they will hence my concern. I have to admit I didn't even know you could restrict a named range to a sheet!

 

[Sektor]

ADVERTISEMENT

You can set sheet scoped named range:
1) in Names Manager
2) in Name Box (to the left of Formula Bar) by prepending sheet name with "!" sign like this: Sheet1!MyRange

 

[miless2111s]

given we've established that the named ranges are in fact for the whole workbook which is what I want what have I don't wrong in my code that means that it wasn't being pulled in?

Many thanks

Miles

 

[BarryL]

ADVERTISEMENT

I have workbooks that have the same named range in each sheet in the same area. Seeing as this is possible I always it best practice to reference sheets().range("Named Range") so it knows exactly which to go to.

on your first macro, without the sheet object, what does the below give you

MsgBox Range("MS_Data").Address

 

[RoryA]

As Barry mentions, you should qualify the range with the sheet it's on - then you don't need to actually activate that sheet first:

Dim MS_Data As Range
Set MS_Data = Worksheets("MS Project Bars Import").Range("MS_Data")
Start = Application.WorksheetFunction.VLookup(Vref, MS_Data, 2, False)

 

[miless2111s]

I have workbooks that have the same named range in each sheet in the same area. Seeing as this is possible I always it best practice to reference sheets().range("Named Range") so it knows exactly which to go to.

on your first macro, without the sheet object, what does the below give you

MsgBox Range("MS_Data").Address

$AX:$BH.
Interestingly when I select a sheet which I know doesn't have any of the named ranges in and then go to the coding window to run the code it debugs out when asked to do run an offset using the range. With the sheet activating code in play this doesn't happen and the same range address is displayed in the message box

 

[miless2111s]

As Barry mentions, you should qualify the range with the sheet it's on - then you don't need to actually activate that sheet first:

Dim MS_Data As Range
Set MS_Data = Worksheets("MS Project Bars Import").Range("MS_Data")
Start = Application.WorksheetFunction.VLookup(Vref, MS_Data, 2, False)

Is that always the case or only in some cases? I ask as normally when I see the range function used it doesn't come with the location qualifier (or I'm just missing it when it is!) - as a I say I'm a rank beginner at this so always keen to learn!

Thanks

Miles