Referencing a worksheet using variable name from array

[mhessnm]

Hello, I am new to the forum and a relatively new beginning VBA programmer in Excel. I have done a lot of looking through various forums and thought I found some code that I could adapt to a project I am working on. The code I am looking for will loop through an array of worksheets and perform a task - there may be a conditional depending on the worksheet.

I found variations of this code in various forums, written by longtime VBA programmers, which seems to have worked for others who have used it. This was copied straight from a forum answer and is very similar to others I've found on websites discussing looping through worksheets. However, I cannot make it work running it as a test, and for the life of me I do not know why it isn't working. The problem comes in the With... statement - when the worksheet is referenced by the variable name, it crashes with a run-time error 9 subscript out of range.

Here is the code:

Sub test_loop()


Dim WshtNames As Variant
Dim WshtNameCrnt As Variant


WshtNames = Array("Prison", "PIIP", "Detox")


For Each WshtNameCrnt In WshtNames


With Worksheets(WshtNameCrnt) 'Runtime error 9 subscript out of range here


Debug.Print "Cell B1 of worksheet " & .Name & " contains " & .Range("B1").Value

End With


Next WshtNameCrnt

End Sub

If possible, I'd like to understand why the program crashes there - I have referenced worksheets with variable names in the past with no problem though the variables were declared as strings. I suspect this has something to do with declaring the variable as a variant (and I admit I don't understand variable declarations as well as I should) and that it is not being passed in string form, but my understanding is that For Each needs the variable declared as variant or object.

I would appreciate any advice or help, and thank you in advance from this beginner.

 

[JLGWhiz]

Intead of this:

WshtNames = Array("Prison", "PIIP", "Detox")
For Each WshtNameCrnt In WshtNames
With Worksheets(WshtNameCrnt) 'Runtime error 9 subscript out of range here
Debug.Print "Cell B1 of worksheet " & .Name & " contains " & .Range("B1").Value
End With
Next WshtNameCrnt

Try this:

Dim i As Long
WshtNames = Array("Prison", "PIIP", "Detox")
For i = LBoundWshtNames) To UBound(WshtNames)
   Debug Print Worksheets(WshtNames(i)).Range("B1").Value 
End With
Next

 

[Fluff]

You're code works fine for me.
Check that the names in the array are spelt correctly including any leading/trailing spaces.

 

[mhessnm]

Okay, now I feel stupid. Thank you, Fluff. I checked and one of the worksheet names had a trailing space that I wasn't aware of. I'm sorry I wasted time with this - but I was literally racking my brain trying to understand why this didn't work and was undone by a simple typing error. Thank you for your patience!

 

[Fluff]

You're welcome & thanks for the feedback