VBA: How to fix "type mismatch"

Question

I have been working on a simple macro that runs a for loop that takes a cell multiples it by the column and sums it before repeating the again for the next cell in the column. The for loops looks fine but when trying to convert a worksheet cell value to an integer it runs into a type mismatch

Dim ws As Worksheet
Dim wssum As Worksheet

'set worksheets to copy values
Set ws = Sheets("sheet1")
Set wssum = Sheets("sheet2")

Dim i As Integer
Dim j As Integer
Dim bumonth As Currency
Dim busum As Currency
Dim bux As Currency



'sort through months
For i = 0 To 11
    'sort through rows the number or rows is hardcoded to the number of apps in sheet
    For j = 0 To 43
        bumonth = 0
        bumonth = CCur(ws.Cells(1, 53 + j).Value * ws.Cells(2 + i, 3 + j).Value)
        busum = busum + bumonth
    Next j
       wssum.Cells(4 + i, 3 + j).Value=  busum
    Next i

The error is happening at line

bumonth = CInt(ws.Cells(1, 53 + j).Value * ws.Cells(2 + i, 3 + j).Value)

what I was hoping this code would do is grab the values from the form in sheet 1 and export the values onto sheet 2.

What is in `Cells(1, 53)` and `Cells(2,3)`? You might need to convert whatever is in those first before multiplying. — dwirony, Jul 02 '19 at 22:02
If the product of those two values is greater than the allowable limit in `Integer`, you need to declare `busum As Long` (or `As Double`). — David Zemens, Jul 02 '19 at 22:04
You want to assign`busum`to wssum.Cell.Value, but reversed it! `wssum.Cells(4 + i, 3 + j).Value = busum`in second last line of code. — ComputerVersteher, Jul 02 '19 at 22:05
The values stored in each of the cells is a larger currency value but even converting the variables to currency gives me this error — VBAlearner1234, Jul 02 '19 at 22:09
Thanks for catching that buy I am running into the type mismatch at line bumonth = CInt(ws.Cells(1, 53 + j).Value * ws.Cells(2 + i, 3 + j).Value) — VBAlearner1234, Jul 02 '19 at 22:09
Check the Cells.Values to be numbers(e.g:`CCur("a" * "b")`raises "Type Missmatch") — ComputerVersteher, Jul 02 '19 at 22:17

score 1 · Answer 1 · answered Jul 02 '19 at 22:31

Most probably the values you are multiplying are not numeric, thus these have to be checked. A possible way to do so is like this:

For j = 0 To 43
    If Not IsNumeric(ws.Cells(1, 53 + j).Value) Then
        Err.Raise 999, Description:="Value on  " & ws.Cells(1, 53 + j).Address & " is not numeric!"
    End If
    If Not IsNumeric(ws.Cells(2 + i, 3 + j).Value) Then
        Err.Raise 999, Description:="Value on  " & ws.Cells(2 + i, 3 + j).Address & " not numeric!"
    End If

    bumonth = 0
    bumonth = CCur(ws.Cells(1, 53 + j).Value * ws.Cells(2 + i, 3 + j).Value)
    busum = busum + bumonth
Next j

Furthermore, as mentioned in the comments, it is not a good idea to use Integer in VBA, as you can easily get an overflow error - Why Use Integer Instead of Long?

Thank you for this. It turns out I made the mistake of mixing up my rows and column in the Cell recall. A simple mistake but one I would have completely overlooked if not for this. — VBAlearner1234, Jul 03 '19 at 14:22
@VBAlearner1234 - You are welcome. Debugging and seeing what causes the mistake is also programming. — Vityata, Jul 03 '19 at 15:05

VBA: How to fix "type mismatch"

1 Answers1