|
Why this "error 9"?
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 -- rpw |
Why this "error 9"?
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 -- HTH... Jim Thomlinson "rpw" wrote: 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 -- rpw |
Why this "error 9"?
That did it - thanks for the help!
-- rpw "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "rpw" wrote: 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 -- rpw |
| All times are GMT +1. The time now is 04:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com