CODEWITHSUNDEEP
vbaexcel
LBPLC
LBPLC

Reputation: 1571

Multiple Objects In With Statement

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?

Upvotes: 4

Views: 5332

Answers (4)

Kaliraj M
Kaliraj M

Reputation: 1

If you don't want many lines of code and want to use With statement the below code will work.

Sub Multiple_Object_Control()
    Dim m As Variant
    
    *'// put objects in a array*
    m = Array(Range("A1"), Range("C3"), Range("B2"))
    
    *'// then loop*
    Dim obj As Variant
    For Each obj In m
        With obj
            .Value = "My value"
            .Font.Color = vbRed
            .Font.Bold = True
        End With
    Next obj
     
End Sub

Hope its helps!!

Upvotes: 0

Alex K.
Alex K.

Reputation: 175776

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], ...

Upvotes: 5

Mat Richardson
Mat Richardson

Reputation: 3606

You could set your range values using a loop instead:-

Sub Test()

Dim i

For i = 1 to 3
    sheets(i).range("A1").value = "TEST"
Next  

End Sub

Upvotes: 2

paul
paul

Reputation: 22001

VBA is trying to use the result of logical 'X AND Y AND Z' as the object in your with statement, which is probably not what you want

You need:

X.Value = "Test"
Y.Value = "Test"
Z.Value = "Test"

rather than 

(X AND Y AND Z).Value = "Test"

Upvotes: 3

Related Questions