CODEWITHSUNDEEP
vbaexcel
Gary's Student
Gary's Student

Reputation: 96763

Avoiding Loops in Setting Cells in Various Worksheets

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.

Upvotes: 5

Views: 102

Answers (2)

Nagendra
Nagendra

Reputation: 11

Instead of using complex arrays,  when you specifically know only two sheets need to be edited. Then we can do in simple.

Sub ThisWorks()

    Sheets("Sheet1").select
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=""x""" 
    Sheets("Sheet3").select
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=""x"""
End Sub

Upvotes: 0

Vasily
Vasily

Reputation: 5782

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

Upvotes: 4

Related Questions