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