Solved: How to resolve out of subscript range error?

[kilbey1]

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.

[VBA]
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)

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

If IsDate(strDate) Then
destRng.Delete
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
[/VBA]

[Jan Karel Pieterse]

What line causes the error?

[kilbey1]

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.

[kilbey1]

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

Gah!

[Jan Karel Pieterse]

Glad you found it!