Worksheet Arrays

S

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
 
C

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)
 
S

scott

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

scott

your code gives same subscript error.

Chip Pearson said:
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)
 
J

JMB

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.
 
G

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
 
S

scott

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


JMB said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top