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

 

[RoryA]

Ideally you should always qualify a Range call with a worksheet, even if it's just ActiveSheet. Otherwise whether or not your code works may be subject to where it is, which workbook and sheet are active, and how a named range was declared. All of that can make it very tricky to debug when it stops working!

 

[miless2111s]

I've done some more experimenting:
1) I added in the worksheets() line above and disabled the activate line
2) I added some code from here to be able to display the full address of MS_Data

Sub Test_match_and_offset()
'test place to see how Match and Offset will work
Dim Start As Date
Dim V_Row As String
Dim MS_Data As Range
Dim Bar_data As Range
Dim Vref As Integer
Dim t As Single
t = Timer
Dim MS_Data_Line_line As String
Dim MS_Data_index As Range
Dim MS_Data_Origin As Range
Dim Bar_data_index As Range
Dim Bar_data_origin As Range
Dim Bar_Data_Line As String
'Worksheets("MS Project Bars Import").Activate ' this doesn't appear to be working
'Set MS_Data = Range("$AX:$BH")
Set MS_Data = Worksheets("MS Project Bars Import").Range("MS_Data") 'why is this not picking up the range???
'Set MS_Data = ActiveWorkbook.Names("MS_Data").RefersToRange
Set MS_Data_index = Range("MS_Data").Columns(1)
Set MS_Data_Origin = Range(Range("MS_Data").Cells(1, 1).Address(0, 0))
'Worksheets("Bar Details").Activate
Set Bar_data = Worksheets("Bar Details").Range("Bar_data")
Set Bar_data_index = Range("Bar_data").Columns(1)
Set Bar_data_origin = Range(Range("Bar_data").Cells(1, 1).Address(0, 0))
'MsgBox "MS_DATA: " & Range("MS_Data").Address '& " bar_details: " & Range("bar-data").Address
Debug.Print "MS_Data: " & Evaluate("ADDRESS(" & MS_Data.Row & "," & MS_Data.Column & ",1,1,""" & MS_Data.Worksheet.name & """)")
Debug.Print "MS_DATA: " & Range("MS_Data").Address
Debug.Print "MS_Data_Origin: " & MS_Data_Origin.Value
Debug.Print "Bar_Data_Origin: " & Bar_data_origin.Value

Now I find that
a) at all times it seems to have the right address for the range
b) it still only works if I start in the correct sheet "MS Project Bars Import", if I start anywhere else it doesn't return the expected data (thought the worksheet address appears to stay the same
1) Starting in the "right" sheet:
MS_Data: 'MS Project Bars Import'!$AX$1
MS_DATA: $AX:$BH
MS_Data_Origin: VID
2) Starting in any other sheet:
MS_Data: 'MS Project Bars Import'!$AX$1
MS_DATA: $AX:$BH
MS_Data_Origin:

So even with the Worksheet modification it doesn't work This is so confusing as it really does feel like it should be working but only the Activate line forces it to work

 

[miless2111s]

Ideally you should always qualify a Range call with a worksheet, even if it's just ActiveSheet. Otherwise whether or not your code works may be subject to where it is, which workbook and sheet are active, and how a named range was declared. All of that can make it very tricky to debug when it stops working!

Right ho! That's a bit of good practice I can learn then Thanks

Now the only challenge is to actually get it to work!

 

[RoryA]

You're not qualifying all the range calls properly. Try this, which uses the variables you did set correctly:

Set MS_Data = Worksheets("MS Project Bars Import").Range("MS_Data") 'why is this not picking up the range???
Set MS_Data_index = MS_Data.Columns(1)
Set MS_Data_Origin = MS_Data.Cells(1)

Set Bar_data = Worksheets("Bar Details").Range("Bar_data")
Set Bar_data_index = Bar_data.Columns(1)
Set Bar_data_origin = Bar_data.Cells(1, 1)

 

[miless2111s]

OK, I see so good practice is that all ranges should be qualified with the sheet location? Why bother using named ranges at all then, why not just use the absolute references? Do I need to do this when calling the contents of one cell, for instance I have a cell which has a value of Yes or No in called "switch" which would currently be called by OnOff = range("switch")? I assume I will need now to use OnOff=workskeets("sheet1").range("Switch") or does it make no difference if I just use OnOff = Worksheets("sheet1").range(A1)?

Thanks for your help

Miles