View Full Version : Solved: How to resolve out of subscript range error?

01-13-2009, 09:16 PM
I have this code which throws an error stating out of subscript range. Even with debugging, I can't what's causing this. Can you see? The error itself is in the loop; there are 10 sheets and the code counts to 10 as expected, but it then throws the error.

Sub FindUpdates2()
'Dim wSht As Worksheet
'Dim destSht As Worksheet
Dim srcRng As Range
Dim destRng As Range
Const cColumnDate = 3 'COLUMN=B
Dim myRow As Integer
Dim vCellValue As Variant

'Set wSht = Worksheets("Open")
Set destSht = Worksheets("Updates")
Set srcRng = Worksheets("Open").Range("C2:C4000")
'Set srcRng = wSht.Range("C2:C4000")
Set destRng = destSht.Range("A2:Q4000")

Application.ScreenUpdating = False

strDate = Application.InputBox(Prompt:="Find Latest Issues As Of:", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=2)

If strDate = "False" Then Exit Sub
'not a date
If Not IsDate(strDate) Then Exit Sub

If IsDate(strDate) Then
strDate = CDate(strDate)
End If

With ThisWorkbook
ReDim SheetWiseMax(1 To .Sheets.Count)
'MsgBox .Sheets.Count

For i = 1 To .Sheets.Count
'MsgBox i

If IsError(Application.Match(Sheets(i).Name, Array("Stats", "Updates", "Top Issues", "Need Status", "Needs Analysis", "Unassigned", "Closed"), 0)) Then
'traverse cells, from last used cell to first one

For myRow = Sheets(i).UsedRange.Rows.Count To 1 Step -1
'get cell value
vCellValue = Sheets(i).Cells(myRow, cColumnDate)
'is value a date?
If IsDate(vCellValue) Then
'compare date, copy row
If vCellValue >= strDate Then
Sheets(i).Rows(myRow).Copy destSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
Next myRow

End If

Next i
End With

Worksheets("Production Stats").Range("E9").Value = strDate
Worksheets("Production Stats").Range("E21").Value = strDate

End Sub

Jan Karel Pieterse
01-13-2009, 11:02 PM
What line causes the error?

01-14-2009, 07:23 AM
Well, it never gets past the loop to execute the write-in of the cell value. It executes 10 times for 10 worksheets, and then throws the error. That's what I'm trying to dig through, but the way I have it set up, I have to steop through it > 100 times because it's looping through each row on 3 spreadsheets.

01-14-2009, 07:26 AM
HELLO! I am such a moron. I finally stepped through and the problem was in the write-in itself. I had renamed the sheet from "Production Stats" to "Stats" -- thus, it's out of range. :P


Jan Karel Pieterse
01-14-2009, 07:42 AM
Glad you found it!