current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a formulated cell containing one string specifying only the worksheets where I want to modify certain cell under certain conditions:

Notes: Worksheets "Total", and "Test1" through "Test20" have the same format (I have more worksheets with different names and formats that I don't want to modify at all).

Let's say I want to modify the cell A5 in worksheets "Total", "Test2", "Test7" and "Test12"

A hidden formulated cell (let's say B50) has been formulated to get the following text string: "Array("Total", "Test2", "Test7", "Test12")". I am assigning this value to a variable expecting it to become part of the vba code where Sheets(SheetNamesList).Select can actually be Sheets(Array("Total", "Test2", "Test7", "Test12")).Select

I am getting a Suscript out of range Error and the line [Sheets(SheetNamesList).Select] is highlighted when debugging.

Below is my code. Thank you for your comments

Sub Active_to_Installed()

Dim SheetNamesList As String

SheetNamesList = Sheets("Names").Range("B50").Value

If Range("B50").Value = "A" Then

Range("B50").Activate
Sheets(SheetNamesList).Select
ActiveSheet.Activate
Range("A5").Select
ActiveCell.FormulaR1C1 = "I"

Else

Range("A5").Activate
Sheets(SheetNamesList).Select
ActiveSheet.Activate
Range("A5").Select
ActiveCell.FormulaR1C1 = "A"

End If
End sub
share|improve this question
    
What is the value of SheetNamesList when the error occurs? Check this in the Locals window, or write ?SheetNamesList in the Immediate window. –  Jean-François Corbett Oct 23 '13 at 18:33
    
+1 For making me learn something new. Thanks. –  Harrison Oct 23 '13 at 18:52

1 Answer 1

up vote 0 down vote

The proper syntax is Sheets(Array("Sheet4", "Sheet5")).Select but you are using the value of a cell which is most likely a string. You will need to convert that string to an array of Sheets

Dim SheetNameArray As Variant
Dim Counter As Integer

SheetNameArray = Split(Worksheets("Names").Range("B50").Value, ",")
Sheets(SheetNameArray).Select

I've actually never selected multiple sheets at the same time, and you wouldn't be able to have multiple active sheets so you can't Activate. You also cannot change the formula of all of them at once here. So to do that you will need to iterate through the sheets as follows.

Dim SheetNameArray As Variant
Dim Counter As Integer

SheetNameArray = Split(Worksheets("Names").Range("B50").Value, ",")
For Each s In SheetNameArray
   Worksheets(s).Range("A5").Formula = "=2+3"
Next s

Sheets(SheetNameArray).Select

Note: You would need to change the value in B50 from Array("Total", "Test2", "Test7", "Test12") to Total, Test2, Test7, Test12

share|improve this answer
    
@igornachov, did this help you? –  Harrison Oct 26 '13 at 1:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.