Run Time Error 13 . Type mismatch

**TERRI LEE** · 07-09-2014, 12:58 PM

Hi

I have recently been having an issue with a macro that i am trying to run. Up until yesterday it ran perfectly but now it is unable to complete running. it generates a message that says "run time error 13, type mismatch". i have attached a sample of my schedule that i am trying to run the macro on. There are 31 pages with a lot more detail on the original schedule. Unfortunately i am unable to attach it here as it is too big.

Excel Test.xlsm

If i debug the macro the following line is highlighted:

If Rng.Offset(0, 6).Value <= 2.05 Then _

Here is the full macro:

Option Explicit

Sub bonuses()
Dim i As Long, lrow As Long, j As Long, lastrow As Long
Dim svalue As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

If Not Evaluate("ISREF(Driver_bonus!A1)") Then
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Driver_bonus"
Else
    Worksheets("Driver_bonus").Delete
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Driver_bonus"
End If

Worksheets("Driver_bonus").Range("A4:L4") = Split("Date,Trucks,Weather Condition, No of Trips,Driver,KM,Diesel Req No,Diesel Filled (l), Diesel Consumption,Trip Sheet No,Weighbridge Ticket No,Tons", ",")
Worksheets("Driver_bonus").Rows(4).Font.Bold = True

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If Len(.Name) <= 2 Then
            lrow = .Range("E" & .Rows.Count).End(xlUp).Row
            For j = 7 To lrow
                If .Range("A" & j).Value <> "" Then
                    lastrow = Worksheets("Driver_bonus").Range("A" & Rows.Count).End(xlUp).Row
                    .Range("A" & j & ":E" & j).Copy
                    Worksheets("Driver_bonus").Range("A" & lastrow + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
                    Worksheets("Driver_bonus").Range("A" & lastrow + 1).PasteSpecial (xlPasteComments)
                    .Range("H" & j & ":K" & j).Copy
                    Worksheets("Driver_bonus").Range("F" & lastrow + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
                     Worksheets("Driver_bonus").Range("F" & lastrow + 1).PasteSpecial (xlPasteComments)
                    .Range("M" & j & ":N" & j).Copy
                    Worksheets("Driver_bonus").Range("J" & lastrow + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
                     Worksheets("Driver_bonus").Range("J" & lastrow + 1).PasteSpecial (xlPasteComments)
                     .Range("Y" & j).Copy
                    Worksheets("Driver_bonus").Range("L" & lastrow + 1).PasteSpecial (xlPasteValuesAndNumberFormats)
                 Worksheets("Driver_bonus").Range("L" & lastrow + 1).PasteSpecial (xlPasteComments)
                 Dim Rng As Range
lrow = Range("C" & Rows.Count).End(xlUp).Row
For Each Rng In Range("C5:C" & lrow)
    If Right(LCase(Rng.Text), 5) <> "total" Then
        '1: if F<=0 and H>0 then F is red
        If Rng.Offset(0, 3).Value <= 0 And Rng.Offset(0, 5).Value > 0 Then _
            Rng.Offset(0, 3).Interior.Color = 255
        '2: if F>0 and H<=0 then H is red
        If Rng.Offset(0, 3).Value > 0 And Rng.Offset(0, 5).Value <= 0 Then _
            Rng.Offset(0, 5).Interior.Color = 255
        '3: if I<=2.05 then I is red
        If Rng.Offset(0, 6).Value <= 2.05 Then _
            Rng.Offset(0, 6).Interior.Color = 255
        '4: if C is empty then C is red
        If Rng.Offset(0, 0).Value = "" Then _
            Rng.Offset(0, 0).Interior.Color = 255
    End If
Next Rng

End If
            Next j
        End If
    End With
Next i

With Worksheets("Driver_bonus")
    .Range("O3").Value = "2.10"
    .Range("N3").Value = "km/litre rate"
    .Range("P3").Value = "pula.litre rate"
    .Range("Q3").Value = "9.76"
    .Range("M4:Y4").Value = Split("Diesel Consumption ( Calaculated),Standard Diesel Consumption @ 2.1km / Litre, , Litres Difference Between Standard - Actual,Pula Difference Standard - Actual,Standard Filled * 1.05 Less Filled,Litres for Deduction,Pula Deduction,Std Bonus,Extra Bonus,Discretionary Bonus between 2.05 & 2.1,Discretionary Bonus between 2 & 2.05,Total", ",")
    
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Sort.SortFields.Add Key:=Range("E5:E" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With .Sort
        .SetRange Range("A4:Y" & lrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    .Range("A4:Y" & lrow).Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(4, 6, 8, 12, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
    lrow = .Range("E" & .Rows.Count).End(xlUp).Row
    
    For i = 5 To lrow
        If .Range("E" & i).Value Like "*Total" And Not .Range("E" & i).Value Like "Grand Total" Then
            .Range("I" & i).Value = .Range("F" & i).Value / .Range("H" & i).Value
            .Range("M" & i).FormulaR1C1 = "=RC[-7]/RC[-5]"
            .Range("N" & i).FormulaR1C1 = "=IF(RC[-8]=0,"""",RC[-8]/R3C15)"
            .Range("P" & i).FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-8]-RC[-2])"
            .Range("Q" & i).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]*R3C17)"
            .Range("R" & i).FormulaR1C1 = "=IF(RC[-2]<0,0,(RC[-4]*1.05)-RC[-10])"
            .Range("S" & i).FormulaR1C1 = "=IF(RC[-1]<0,RC[-1],0)"
            .Range("T" & i).FormulaR1C1 = "=+RC[-1]*R3C17"
            .Range("U" & i).FormulaR1C1 = "=IF(RC[-8]>=2.1,2000,0)"
            .Range("V" & i).FormulaR1C1 = "=IF(RC[-9]>=2.2,RC[-1]*0.25,0)"
            .Range("W" & i).FormulaR1C1 = "=IF(AND(2.05<RC[-10],RC[-10]<2.1),1000.00, 0)"
            .Range("X" & i).FormulaR1C1 = "=IF(AND(2<=RC[-11], RC[-11]<=2.05),500.00, 0)"
            .Range("Y" & i).FormulaR1C1 = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1]"
            .Rows(i).Font.Bold = True
            svalue = Left(.Range("E" & i).Value, Len(.Range("E" & i).Value) - 6)
             .Range("C" & i).FormulaR1C1 = "=Index('MINE employment codes'!C[-2],Match(""" & svalue & """, 'MINE employment codes'!C[-1],0))"
             .Range("C" & i).Value = .Range("C" & i).Value
        ElseIf .Range("E" & i).Value = "Grand Total" Then
            .Rows(i).Font.Bold = True
            .Rows(i).Font.Color = 255
            .Range("I" & i).Value = .Range("F" & i).Value / .Range("H" & i).Value
        End If
    Next i
    
    .Cells.EntireColumn.AutoFit
   
    .Range("B4:Y" & lrow).NumberFormat = "0.00"

    With .Range("A3:Y" & lrow)
        .Font.Size = 8
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
    
        With .Borders()
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
   End With
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

Thanks for your help
Terri