Type mismatch, not sure why

Question

recently my macro hasn't been working out, it keeps getting an error mismatch on this line 'v = v + Sheets("members inkind").Cells(i, 6) * Sheets("members inkind").Cells(i, 7)'

and i can't figure out why, it used to work but recently it has been having this error, if both my Cells(i,6) and cells(i,7) are numbers, why does it still have an error?

Sub Macro2()
'
' Macro2 Macro

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Members Cash"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Members inkind"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Bailment"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Projects Cash"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Projects Inkind"
Sheet1.Select
Range("A1:M1").Copy
Sheets("Members Cash").Select
Range("A1:M1").PasteSpecial Paste:=xlValues
Columns("A:N").EntireColumn.AutoFit
Sheets("Members inkind").Select
Range("A1:M1").PasteSpecial Paste:=xlValues
Columns("A:N").EntireColumn.AutoFit
Sheets("Bailment").Select
Range("A1:M1").PasteSpecial Paste:=xlValues
Columns("A:N").EntireColumn.AutoFit
Range("A1:M1").Copy
Sheets("Projects Cash").Select
Range("A1:M1").PasteSpecial Paste:=xlValues
Range("A1:M1").Copy
Sheets("Projects Inkind").Select
Range("A1:M1").PasteSpecial Paste:=xlValues
With Sheet1
    .AutoFilterMode = False
    .Select
    Range("$A$1:$M$999").AutoFilter Field:=2, Criteria1:=Array("*discussion*"), Operator:=xlFilterValues
    Range(Cells(2, 2), Cells(2, 2).End(xlDown)).EntireRow.Delete
    .AutoFilterMode = False
End With
    Sheet1.AutoFilterMode = False
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=9, Criteria1:="FY17"
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=2, Criteria1:=Array("*Membership*"), Operator:=xlFilterValues
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=4, Criteria1:=("<>"), Operator:=xlAnd, Criteria2:=("<>0")


    Sheet1.Select
    Range(Cells(1, 1), Cells(1, 1).End(xlToRight).End(xlDown)).Copy
    Sheets("Cash donations").Select
    Range("A2").PasteSpecial Paste:=xlValues
    Range("A2").EntireRow.Delete

    Sheet1.AutoFilterMode = False
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=9, Criteria1:="FY17"
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=2, Criteria1:=Array("*Membership*"), Operator:=xlFilterValues
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=5, Criteria1:=("<>"), Operator:=xlAnd, Criteria2:=("<>0")

    Sheet1.Select
    Range(Cells(1, 1), Cells(1, 1).End(xlToRight).End(xlDown)).Copy
    Sheets("Members inkind").Select
    Range("A2").PasteSpecial Paste:=xlValues
    Range("A2").EntireRow.Delete

        Sheet1.AutoFilterMode = False
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=9, Criteria1:="FY17"
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=2, Criteria1:=Array("*Bailment*"), Operator:=xlFilterValues
    Sheet1.Range("$A$1:$M$999").AutoFilter Field:=5, Criteria1:=("<>"), Operator:=xlAnd, Criteria2:=("<>0")

    Sheet1.Select
    Range(Cells(1, 1), Cells(1, 1).End(xlToRight).End(xlDown)).Copy
    Sheets("Bailment").Select
    Range("A2").PasteSpecial Paste:=xlValues
    Range("A2").EntireRow.Delete

    Sheets("Members cash").Cells(1, 4).End(xlDown).Offset(1, 0) = Application.WorksheetFunction.Sum(Sheets("Members Cash").Range("D2:D999"))
    Sheets("Bailment").Cells(1, 5).End(xlDown).Offset(1, 0) = Application.WorksheetFunction.Sum(Sheets("Bailment").Range("E2:E999"))
    v = 0
    Row = Sheets("Members inkind").Cells(1, 1).End(xlDown).Row

    For i = 2 To Row

        If IsEmpty(Sheets("members inkind").Cells(i, 7)) = True Then
        v = v + Sheets("members inkind").Cells(i, 5)
        Else
        v = v + Sheets("members inkind").Cells(i, 6) * Sheets("members inkind").Cells(i, 7)
        End If

    Next i

 Sheets("Members inkind").Cells(1, 5).End(xlDown).Offset(1, 0) = v

end sub

Thank you!

Cells(i, 6) and or Cells(i, 7) contains non-numerics such as error value, strings etc. or really small or large numbers. Start by using IsError and IsNumeric along with IsEmpty — cyboashu

Marcucciboy2 Marcucciboy2 · Accepted Answer · 2017-12-12T05:11:37

You might want to consider trying

v = v + Sheets("members inkind").Cells(i, 6).Value * Sheets("members inkind").Cells(i, 7).Value

Type mismatch, not sure why

1 Answers