Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Avoiding Loops in Setting Cells in Various Worksheets

Tags:

excel

vba

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.

like image 804
Gary's Student Avatar asked Sep 29 '22 19:09

Gary's Student


1 Answers

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
like image 194
Vasily Ivoyzha Avatar answered Oct 11 '22 14:10

Vasily Ivoyzha