Why this "error 9"?

[rpw]

Hi all,

The snippet of code below stops on the first line that uses "UsedRange2".
The snippet is a duplicate of a prior snippet in the same Sub that does run
OK (I've changed variable names to have the number 2 at then end of the name
for this second snippet and 3 for the third copy). I can't see why this
would stop with the "subscript out of range - error 9" error. Can anyone
help?

For Each WkSht1 In OldWB1.Worksheets
SheetName = WkSht1.Name
If SheetName = "Blank Form" Then
Else
<< (stops on this line)>> Set UsedRange2 =
Sheets(WkSht1.Name).Range("K29:K32,K36:K44")
For Each cell In UsedRange2
SumArray2(MyCell2) = SumArray2(MyCell2) + cell.Value
Next cell
MyCell2 = MyCell2 + 1
End If
Next WkSht1

 

[Jim Thomlinson]

Try this...

For Each WkSht1 In OldWB1.Worksheets
SheetName = WkSht1.Name
If SheetName = "Blank Form" Then
Else
Set UsedRange2 = WkSht1.Range("K29:K32,K36:K44")
For Each cell In UsedRange2
SumArray2(MyCell2) = SumArray2(MyCell2) + cell.Value
Next cell
MyCell2 = MyCell2 + 1
End If
Next WkSht1

 

[rpw]

That did it - thanks for the help!
--
rpw

Try this...

For Each WkSht1 In OldWB1.Worksheets
SheetName = WkSht1.Name
If SheetName = "Blank Form" Then
Else
Set UsedRange2 = WkSht1.Range("K29:K32,K36:K44")
For Each cell In UsedRange2
SumArray2(MyCell2) = SumArray2(MyCell2) + cell.Value
Next cell
MyCell2 = MyCell2 + 1
End If
Next WkSht1