Trying to find a way Prevent a Type Mismatch error from Occring in Excel VBA

Question

Sub Calculate_NNA_NMEXRegionUnits_Pacific()
Dim a, rand, AsInteger As Integer
For a = 8 To 42 
 Cells(26, a) = Round(Cells(27, a) * Cells(27, 7) * (Cells(22, 2) - Cells(22, 3) - Cells(20, 7)), 0)
Next a

I'm making a program that performs a set of calculation on a range (26,a). The formula data type is variant/integer, while the data type on the range is variant/object/range. I am curious if I can ensure both sides of this equation are the same data type, I was looking at ways to change the formula, I've also worked with type conversion formulas. I have not been successful. I am asking for assistance to prevent a Type mismatch from occurring on row 4 so my code can proceed.

rerand1:
    rand = Application.WorksheetFunction.RandBetween(9, 27) 'Excludes base trim in random addition because of restrictions
    If Cells(27, rand) = 0 Then
        GoTo rerand1 'Wont change a 0 value, because it might be intentionally zero.
    Else
        If Cells(26, 7) < Cells(22, 2) - (Cells(22, 3) + Cells(20, 7)) Then Cells(26, rand) = Cells(26, rand) + 1 'If there is less than the total, add.
        If Cells(26, 7) > Cells(22, 2) - (Cells(22, 3) + Cells(20, 7)) Then Cells(26, rand) = Cells(26, rand) - 1 'If there is more than the total, subtract.
    End If
End If
End Sub

This is the next step of my code if it is needed to solve the problem

Try declaring rand as a long, then rand=clng(Application.WorksheetFunction.RandBetween(9, 27) ) Not sure what Dim a, rand, AsInteger As Integer is meant to do, you have a variable called AsInteger, which is the only one declared As Integer. — Nathan_Sav

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-03-06T15:29:47

Type mismatch can occur not only because you have different value types. You can also get that error if there is a formula error in that cell. For example if you type =0/0 in cell A1 and then in immediate window if you execute ?Val([A1].value), you will get the Type Mismatch error.

Here is one way to achieve what you want. Try and store the value in a temp variable and then use that for the comparision.

Dim tmpVal As Integer

rand = Application.WorksheetFunction.RandBetween(9, 27)

On Error Resume Next
tmpVal = Cells(27, rand)
On Error GoTo 0

If tmpVal = 0 Then

Else

End If

Trying to find a way Prevent a Type Mismatch error from Occring in Excel VBA

1 Answers