A VBA Runtime Error 13: "Type Mismatch" is hitting my code hard. This is the line I’m hung up on:
Man, I can’t figure out what I’m doing wrong.
What is VBA Runtime Error 13: Type Mismatch?
VBA Runtime Error 13: Type mismatch occurs when VBA assigns one type of data to a variable and it gets another.
The most common VBA Runtime Error 13 occurs when you have a variable that stores a number such as an VBA Integer data type or a VBA Double data type and you try to assign a text value that VBA cannot convert into a number value.
For example, each line of the following code will generate a VBA Runtime Error 13 type mismatch error:
Dim nNumPayments As Integer
Dim dPayAmount As Double
Dim dPayDate As Date
nNumPayments = "12 Payments"
dPayAmount = "12 dollars 12 cents"
dPayDate = "Tuesday, June 12"
Another type of VBA Error 13 occurs when you have a VBA Boolean variable that only accepts TRUE or FALSE values. Generally if you will use a logical condition or test that evaluates to TRUE or FALSE value
Dim bPaymentOK As Boolean
Dim dPayAmount As Double
dPayAmount = 12.12
' Evaluate to TRUE or FALSE
bPaymentOK = dPayAmount > 0
bPaymentOK = Not dPayAmount = 0
bPaymentOK = 1
'/ VBA ERROR 13 -
'/ "YES" Does Not = TRUE or FALSE
bPaymentOK = "YES"
VBA Logical Loops: A Trick
In this case there is a tricky situation. The line that has an error is a VBA DO WHILE loop.
The DO WHILE loop evaluates and is expecting a Boolean TRUE or FALSE value so that it can decide to either continue or stop looping.
The problem is that the value it is evaluating is a range's Text property.
The TEXT in that cell is not likely to convert easily into a Boolean TRUE or FALSE value. If that text data type is anything other than a TRUE or FALSE value it throws off a VBA Error 13 type mismatch because its looking for a Boolean and you are giving it a text value.
This loop is part of a dynamic looping technique I teach as part of my Excel VBA courses so I know where this is going. The dynamic looping technique loops through a list of data until we hit a blank cell, which would be the end of the list.
The correct code is:
Do Until ThisWorkbook.Worsheets("Data").Range("A1").Offset(nRowOffset).Text = ""
The reason that this works is that as VBA is looping down the list of cells it is constantly checking to see if the text property of a cell is blank by evalauting it as equal to a null string (= "").
If the cell has a non-null text value it evaluates to a FALSE value and continue the loop, if the cell is blank it evaluates to a TRUE value and the loop will stop.