I can set a specific cell in multiple worksheets to the same value without using a loop with something like:
Sub ThisWorks()
Sheets(Array("Sheet1", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("B9").Select
ActiveCell.FormulaR1C1 = "=""x"""
End Sub
Running the macro above places the same value in all the B9s in the worksheets included in the Array()
I am trying to do the same thing by creating and using the Object equivalent to the above code:
Sub ThisPartiallyWorks()
Dim obj As Object
Set obj = Sheets(Array("Sheet1", "Sheet3"))
obj.Select
Range("A2").Formula = "=""x"""
End Sub
This runs without error, but only one worksheet gets the ="x" in the desired cell.
What am I doing wrong??
EDIT#1
Vasily's Post gave me the clue................this version appears to work:
Sub Finally()
Dim obj As Object
Set obj = Sheets(Array("Sheet1", "Sheet3"))
obj.Select
obj(1).Activate
Range("B9").Select
ActiveCell.FormulaR1C1 = "=""x"""
End Sub
Although this seems to violate the notion that Select can be avoided.
I understand this is not exactly what you want, but as one of the options for reducing the coding.
Sub test()
Dim obj As Object, i&
Set obj = Sheets(Array("Sheet1", "Sheet3"))
For i = 1 To obj.Count: obj(i).[B9].Formula = "=""x""": Next
End Sub
EDIT#1
might look like this
Sub Finally()
Dim obj As Object
Set obj = Sheets(Array("Sheet1", "Sheet3"))
obj.Select: obj(1).[B9].Activate: ActiveCell.Formula = "=""x"""
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With