Worksheet Arrays

[scott]

I'm building a sub that will loop through only the worksheets contained
within an array and then perform a test on each cell of the "array"
worksheets. Below, I'm trying to only access the worksheets named "Jan",
"Feb" or "Mar".

I'm getting an error saying "subscript out of range" on the line

For Each sh In Worksheets(Array("Jan", "Feb", "Mar"))

Can someone help me with the array syntax?


'CODE: ****************************************

Dim cell As Range, sh As Worksheet
Dim sMsg, iStyle, sTitle, Response

For Each sh In Worksheets(Array("Jan", "Feb", "Mar"))
For Each cell In sh.UsedRange

If cell.Interior.ColorIndex = 19 Then

sMsg = "name: " & sh.Name & vbCrLf & _
"Cell Address: " & cell.Address & vbCrLf & vbCrLf & _
"Do you want to continue ?"
iStyle = vbYesNo + vbInformation + vbDefaultButton2
sTitle = "Find Name"

Response = MsgBox(sMsg, iStyle, sTitle)
If Response = vbYes Then
'Continue
ElseIf Response = vbNo Then
Exit For
End If

End If

Next cell
Next sh

 

[Chip Pearson]

Try something like

Dim Arr() As Variant
Dim N As Long
Dim WS As Worksheet

Arr = Array("Jan", "Feb", "Mar)
For N = LBound(Arr) To UBound(Arr)
Set WS = Worksheets(Arr(N))
' do something with WS
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[scott]

what would the syntax look like to do a a "For each cell in current
worksheet" look like within your code?

 

[scott]

your code gives same subscript error.

Try something like

Dim Arr() As Variant
Dim N As Long
Dim WS As Worksheet

Arr = Array("Jan", "Feb", "Mar)
For N = LBound(Arr) To UBound(Arr)
Set WS = Worksheets(Arr(N))
' do something with WS
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

It would be pretty similar to what you originally had. Where Chip put 'do
something with worksheet, you would use:

For Each rngCell In WS.UsedRange
'Your test here
Next rngCell

(except I habitually use rngCell as my loop variable. For me, cell is too
close to the cells reserved word). If you're still getting subscript out of
range, I can only suggest you double check your sheet names (trailing spaces,
perhaps?). There was a quote missing on "Mar", but that would throw a
compile error, so I assume you've fixed it. Otherwise, Chip's code works
fine for me as posted.

 

[Gary Keramidas]

give this a try and watch for wordwrap from outlook express

Option Explicit
Sub test()
Dim cell As Range, sh As Worksheet
Dim sMsg, iStyle, sTitle, Response
Dim arr As Variant
arr = Array("Jan", "Feb", "Mar")
Dim i As Long
For i = LBound(arr) To UBound(arr)
Set sh = Worksheets(arr(i))
For Each cell In sh.UsedRange
If cell.Interior.ColorIndex = 19 Then
sMsg = "name: " & sh.Name & vbCrLf & _
"Cell Address: " & cell.Address & vbCrLf &
vbCrLf & _
"Do you want to continue ?"
iStyle = vbYesNo + vbInformation + vbDefaultButton2
sTitle = "Find Name"
Response = MsgBox(sMsg, iStyle, sTitle)
End If
If Response = vbYes Then
'Continue
ElseIf Response = vbNo Then
Exit For
End If
Next ' cell
Next ' sheet
End Sub

 

[scott]

thanks. error was that no "Feb" sheet existed.

It would be pretty similar to what you originally had. Where Chip put 'do
something with worksheet, you would use:

For Each rngCell In WS.UsedRange
'Your test here
Next rngCell

(except I habitually use rngCell as my loop variable. For me, cell is too
close to the cells reserved word). If you're still getting subscript out
of
range, I can only suggest you double check your sheet names (trailing
spaces,
perhaps?). There was a quote missing on "Mar", but that would throw a
compile error, so I assume you've fixed it. Otherwise, Chip's code works
fine for me as posted.