| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Shortcut key for "Paste Options" and "Error Checking" buttons? | Excel Discussion (Misc queries) | |||
| What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
| "Clean Me" Macro is giving "#VALUE!" error in the Notes field. | Excel Programming | |||
| Getting "compile error" "method or data member not found" on reinstall | Excel Programming | |||
Linear Mode
