CODEWITHSUNDEEP
excel-2010
user1829871
user1829871

Reputation: 11

VBA Run Time Error '13' Type mismatch

We recently updated to Windows 7 with Office 2010 and now some of our Excel macros are not working. A sample code is below, I am getting the Run Time Error '13' but cannot find where the types are mismatched. 

Private Sub btnUpdate_Click()

Dim lngLastRow As Long
Dim arrRevSource(0 To 9) As Long

Dim i As Integer

lngLastRow = Sheets("Test Log").Range("A1").End(xlDown).Row

For i = 2 To lngLastRow

If Sheets("Test Log").Cells(i, 7).Value <> "incl." And Sheets("Test Log").Cells(i,    7).Value <> "" And Sheets("Test Log").Cells(i, 7).Value <> "VOID" Then 'protect from  entries other than numbers

    Select Case Sheets("Test Log").Cells(i, 9).Value
        Case "A033", "A034", "A225", "A226" 'Cap2000
            arrRevSource(0) = arrRevSource(0) + Sheets("Test Log").Cells(i, 7).Value

          Case "0611", "0612", "0620", "0621", "0622", "0623", "0815", "0816", "0818", "0819", "0820", "0821" 'EVAP Development
            arrRevSource(1) = arrRevSource(1) + Sheets("Test Log").Cells(i, 7).Value

        Case "A084", "A173", "A220", "A224", "A290", "A294", "A301" 'Cert
            arrRevSource(2) = arrRevSource(2) + Sheets("Test Log").Cells(i, 7).Value

        Case "0792", "0794" 'Diesel In Use Enforcement
             arrRevSource(3) = arrRevSource(3) + Sheets("Test Log").Cells(i, 7).Value

        Case "0608", "0654" 'AMG
            arrRevSource(4) = arrRevSource(4) + Sheets("Test Log").Cells(i, 7).Value

        Case "0588" 'G Class Development
            arrRevSource(5) = arrRevSource(5) + Sheets("Test Log").Cells(i, 7).Value

          Case "0543", "0545", "0547", "0548", "A009", "A093", "A252", "A295", "A296" 'Third Party"

            arrRevSource(6) = arrRevSource(6) + Sheets("Test Log").Cells(i, 7).Value

       Case "A002", "A237" 'OBD
           arrRevSource(7) = arrRevSource(7) + Sheets("Test Log").Cells(i, 7).Value

       Case "A268" 'FETS
           arrRevSource(8) = arrRevSource(8) + Sheets("Test Log").Cells(i, 7).Value


           'For All Other Sources of Revenue
       Case "A074", "A109", "A168", "A185", "A199", "A208", "A217", "A239", "A270", "0666", "0669" 'Other
            arrRevSource(9) = arrRevSource(9) + Sheets("Test Log").Cells(i, 7).Value

        'Case Else 'Other
            'arrRevSource(9) = arrRevSource(9) + Sheets("Test Log").Cells(i, 7).Value


    End Select

End If

Next

For i = 1 To 10

    Sheets("Revenue Sources").Cells(3, i + 1).Value = arrRevSource(i - 1)

Next

End Sub

Upvotes: 1

Views: 1224

Answers (1)

Jook
Jook

Reputation: 4682

I am not sure, if this will solve all of your problems, but I would definetly change it:

Dim i as integer 'should be as long!

For i = 2 To lngLastRow

And I would say your IF-Protection against non-numbers does not work right or I misunderstood there something. You might want to try to use this line for your select:

Select Case CStr(Sheets("Test Log").Cells(i, 9).Value)

Upvotes: 1

Related Questions