'Type Mismatch' Error with .Find() method in VBA

Question

I'm writing a sub in Excel VBA, and it keeps giving me a 'Type Mismatch' error message when trying to assign the result of a .Find() to a Range variable. I feel pretty confident that my types are appropriate, so perhaps there's a syntax error somewhere?

Help would be GREATLY appreciated: (Line preceded by asterisks is where error is thrown)

Sub totalTiger(fCode As String, project As String, ttls() As Double)

    'Set shorcuts to Worksheets
    Dim this As Workbook:   Set this = ThisWorkbook
    Dim proj As Worksheet:  Set proj = this.Worksheets(project)

    'Dim req variables
    Dim tRng As Range:  Set tRng = proj.Range("A:A").Find(What:="Program Description")   'Establish where Staff data ends and Tiger data begins
    ***Dim rng As Range:   Set rng = proj.Range("C:C").Find(What:=fCode, After:=tRng)   'First fCode entry***

    'For each fCode entry BEFORE the Tiger data, sum the assignments by month
    Do While Not rng Is Nothing And rng.row > tRng.row

        'For each month
        For col = 4 To 15

            'Add this month's assignment to our running total
            ttls(col - 4) = ttls(col - 4) + CDbl(proj.Cells(rng.row, col).Value)

        Next

        'Update the rng reference
        Set rng = proj.Range("C:C").Find(What:=fCode, After:=rng)

    Loop

End Sub

genespos genespos · Accepted Answer · 2015-02-11T18:22:32

I think the problem is in "After:=tRng": it may be out of the range of the "find"

Dim rng As Range:   Set rng = proj.Range("C:C").Find(What:=fCode, After:=tRng)

Try removing "After:=tRng" and, if it works after removing, then try to insert a correct range.

Dim rng As Range:   Set rng = proj.Range("C:C").Find(What:=fCode)

I'm not sure that's what you need, but you can try:

Dim rng:    Set rng = proj.Range("C:C").Find(What:=fCode, After:=proj.Range("C" & tRng.Row))

It finds the first 'fCode' starting by the row where was found "Program Description"

'Type Mismatch' Error with .Find() method in VBA

3 Answers