Using range array with worksheet array

[gordsky]

Hi,
Hoping someone can sort this one for me.
I have an array of worksheets called MySht and an array of ranges called MyRng. What I am trying to do is loop through the range array with the first worksheet and then move to the second worksheet and do the same. I was hoping to do it something like :

For each ws in MySht
   For each Rng in MyRng
      set srchrng = ws.rng
         'CODE THEN DOES SOME OTHER BITS
   next rng
next ws

code is failing on the ws.rng bit

what am i doing wrong??

 

[Joe4]

A range value is already a set range, with worksheet reference built into it (if you do not qualify the range with a worksheet reference, it defaults to the active sheet).
So it doesn't make sense to prefix a range variable with a worksheet variable (since the range variable is already attached to a sheet, by definition).

Here is a simple example to show that:

Sub Test()

    Dim ws As Worksheet
    Dim rng As Range
   
    Set ws = Sheets("Sheet2")
    Set rng = Range("A1")
   
    MsgBox rng.Value
    'MsgBox ws.rng.Value
   
End Sub

If you run this code, it will return the value from cell A1 of the ActiveSheet.
However, if you uncomment the second MsgBox (and try to put a worksheet reference in front of the range), you will see it is not a valid reference and you will get an error.

To do what you want, it would be better to store the range addresses as strings in your array, and then refer to them like ws.Range(rng), as shown in this simple example:

Sub Test2()

    Dim ws As Worksheet
    Dim rng As String
   
    Set ws = Sheets("Sheet2")
    rng = "A1"
   
    MsgBox ws.Range(rng).Value
   
End Sub

 

[Alex Blakenburg]

Please show us all your code.

 

[gordsky]

A range value is already a set range, with worksheet reference built into it (if you do not qualify the range with a worksheet reference, it defaults to the active sheet).
So it doesn't make sense to prefix a range variable with a worksheet variable (since the range variable is already attached to a sheet, by definition).

Here is a simple example to show that:

Sub Test()

    Dim ws As Worksheet
    Dim rng As Range
  
    Set ws = Sheets("Sheet2")
    Set rng = Range("A1")
  
    MsgBox rng.Value
    'MsgBox ws.rng.Value
  
End Sub

If you run this code, it will return the value from cell A1 of the ActiveSheet.
However, if you uncomment the second MsgBox (and try to put a worksheet reference in front of the range), you will see it is not a valid reference and you will get an error.

To do what you want, it would be better to store the range addresses as strings in your array, and then refer to them like ws.Range(rng), as shown in this simple example:

Sub Test2()

    Dim ws As Worksheet
    Dim rng As String
  
    Set ws = Sheets("Sheet2")
    rng = "A1"
  
    MsgBox ws.Range(rng).Value
  
End Sub

Joe4 - string option worked perfectly. thank you

 

[Joe4]

You are welcome.
Glad we were able to help!