Run-time error '13' Type mismatch

[vahnx]

This code worked before the weekend and I pulled a successful (for the most part) extraction from my db. Now today I need to re-run it but it's saying there's a type mismatch.

'r' was an integer which didn't seem right but it's still not working as a string. It's frustrating when code works before the weekend and doesn't work after.

' Extract
' by 
' 15/04/2011


' Press F5 on this function to clear the immediate window
'
'
'
Public Function ClearImmediateWindow()
    Application.SendKeys "^g ^a {DEL}"
End Function
'
'
'
'



' Extracts HOLEID, From, To, m, Au g/t (f),  Comment
'          A       F    G   H    Z           AB
'
' Where Column AB has   no Pulp Metallics
'               E has   no DUP, BLK, STD, QTR, HLF
'
'
'
'
'

Sub Extract()

    Dim r As String    ' Current row
    Dim cnt As Integer  ' Matched criteria counter
    cnt = 1

    
    Dim H_HOLEID, F_FROM, T_TO, M_M, A_AUGTf, C_COMMENT As String   ' Holds the information to extract
    
    Debug.Print ""
    Debug.Print "ROW" & "   " & "Sample #" & "   " & "Hole ID" & "   " & "From" & "   " & "To" & "   " & "m" & "   "; "Au g/t (f)" & "   " & "Comment"
    Debug.Print ""
    
    
    For i = 2 To 15571    ' Count through the entire database
        r = CStr(i)   ' Convert row to a string


    ' Matches criteria in description

        If InStr(Range("A" & r), "GAR") And Not InStr(Range("AB" & r), "Pulp Metallics") And Not InStr(Range("E" & r), "DUP") _
            And Not InStr(Range("E" & r), "BLK") And Not InStr(Range("E" & r), "STD") And Not InStr(Range("E" & r), "QTR") _
            And Not InStr(Range("E" & r), "HLF") Then
        
            cnt = cnt + 1               ' Increase matched item counter

        ' Assigns values to variables for further play such as printing or inserting
                    
        H_HOLEID = Range("A" & r).Value
            F_FROM = Range("F" & r).Value
            T_TO = Range("G" & r).Value
            M_M = Range("H" & r).Value
            A_AUGTf = Range("Z" & r).Value
            C_COMMENT = Range("AB" & r).Value

            ' Places extract along the right hand side of the database, ready
        ' for copy and paste into extraction sheet
            
            Range("CE" & cnt) = H_HOLEID
            Range("CF" & cnt) = F_FROM
            Range("CG" & cnt) = T_TO
            Range("CH" & cnt) = M_M
            Range("CI" & cnt) = A_AUGTf
            Range("CJ" & cnt) = C_COMMENT
        
        End If
    Next i
    
    
    Debug.Print "Total " & CStr(cnt) & " Results!"
    
End Sub

*EDIT* User error

 

[Ruddles]

The code you posted above, is it the exact code that worked last week? If not, we need to see the code which was working rather than a version which you've amended and which may never have worked.

Which line of code results in the error? And what's the value of each of the variables referenced in that line?

 

[vahnx]

Ah I should of updated my edit bigger =p
It was a user error, the database had a column pushed over. Person modified it without telling me.

Thanks for the help through, the code works fine!

 

[Ruddles]

It would be a good idea to protect it from that happening in future if you have worksheet fairies in the house.