"Subscript out of range" error when referencing another sheet

Question

I can't get past the "subscript out of range" error arising from the code below. It throws the error when I first set the found variable. The sub is for each sheet in the workbook, to search for an employee from a known column and identify the row, then use that row to sum a variable. Then it fills a cell in the active sheet with that total. "col" is specified in the main sub.

nDate = Range("B3")
Dim startDate As Date
startDate = Range("B2")
Dim emp As String
emp = Range("B8")
Dim rw As String
n = 0

Do While True
    Range("B99") = nDate
    stringDate = Range("B99").Text

    Set found = Worksheets(stringDate).Range("D38:D144").Find(emp, LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            If found = emp Then
                rw = found.row
                n = Worksheets(stringDate).Range(col + rw)
                tot = tot + n
            End If

    If nDate = startDate Then
            Exit Do
        End If
    nDate = nDate - 1
Loop

Range(col + "3") = tot

I have similar Subs in my code that compiled just fine. The only difference is that in the sub above, I'm searching a range. Below is the other sub that doesn't throw the error.

n = 0
Dim startDate As Date
Dim endDate As Date
startDate = Range("B2")
nDate = Range("B3")

Do While True
    Range("B99") = nDate
    stringDate = Range("B99").Text
    n = Worksheets(stringDate).Range(col + rw)
    tot = tot + n
    If nDate = startDate Then
        Exit Do
    End If
    nDate = nDate - 1
Loop

Range(col + "3") = tot

I know there are similar questions about the same error, but none deal with referencing external sheets. Any suggestions on how to debug?

Do you have Option Explicit at the top of every module? If not, your code will compile but may throw errors at run-time. — Mark Fitzgerald
I don't have that. I get the error weather I compile the code or run it. — Dane
You set stringDate as a Date (which in Excel is just a number). Then when you call Worksheets(activeDate) you are trying to grab a worksheet by index with an index that is likely in the thousands (how dates are represented). You need to ensure that stringDate is an actual string and that it matches a valid sheet name. So Dim stringDate as String. I am unsure how your sheets are named so you may need to reformat the stringDate variable appropriately. — JG7
I ran into that problem writing it originally. That's why set the "stringDate" variable as a string by getting the .text from a temporary cell for the current iteration of the date. That way, stringDate is now a string. — Dane
Would you mind telling me what a Debug.print statement of steingDate is right after you set it to B99’s value? — JG7

Excelosaurus Excelosaurus · Accepted Answer · 2017-10-26T03:48:21

Add this at the beginning of the sub/function:

Dim targetWs As Excel.Worksheet

Then change the loop to:

Do While True
    Range("B99") = nDate
    stringDate = Range("B99").Text

    Set targetWs = Nothing
    Set found = Nothing

    On Error Resume Next
    Set targetWs = Worksheets(stringDate)
    On Error GoTo 0

    If targetWs Is Nothing Then
        MsgBox "Worksheet not found: " & stringDate
    Else
        Set found = targetWs.Range("D38:D144").Find(emp, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    End If

    If Not found Is Nothing Then
        If found = emp Then
            rw = found.row
            n = targetWs.Range(col + rw)
            tot = tot + n
        End If
    End If

    If nDate = startDate Then
        Exit Do
    End If
    nDate = nDate - 1
Loop

If a message box shows up, you'll know you've hit a worksheet name that's not part of the Worksheets collection.

"Subscript out of range" error when referencing another sheet

2 Answers