Multiple Objects In With Statement

Question

I'm wondering why this simple code throws a "Object Required" error when I attempt to run it. I can't really find any reason why VBA would have trouble andling this. It compiles OK, but fails to run.

Sub Test()

Dim X, Y, Z As Range

Set X = Sheets(1).Range("A1")
Set Y = Sheets(2).Range("A1")
Set Z = Sheets(3).Range("A1")


With X And Y And Z
.Value = "Test" 'FAILS HERE
End With


End Sub

Im sure its something small, or maybe VBA just can't handle this?

Answer 1

If you do this a lot, create a helper routine:

Sub setValueOf(value As String, ParamArray ranges())
   Dim i As Long
   For i = 0 To UBound(ranges)
      ranges(i).value = value
   Next
End Sub

You can then pass 1 or more ranges;

setValueOf "Test", Sheets(1).Range("A1"), Sheets(2).Range("A1"), Sheets(3).Range("A1"), [B5], ...