String to Integer with CInt-CodePudding

I am copying a cell to a variable, converting it to an integer in a second variable, adding 1, and passing it back to the cell with the following code:

Option Explicit


Dim copyData As String
Dim copyNum As Integer

'read data
copyData = Cells(1, 4)

'convert to int
copyNum = CInt(copyData)

'advance by 1
copyNum = copyNum   1

'print back to the sheet
Cells(1, 4) = copyNum

When this runs i get a type mismatch error when the line copyNum = CInt(copyData) runs. Why is it throwing an error? i don't understand why it thinks there's a mismatch.

PS, this error still happens if I declare copyNum as a double or a long.

CodePudding user response：

As mentioned in comments probably you have empty cell causing the error. Val() function converts String to Double. Empty cell or non-numeric value will return 0. You can add some checks for that cases. You can try this code:

Cells(1, 4) = Val(Cells(1, 4).Value)   1

Or change copyNum = CLng(copyData) to copyNum = Val(copyData) if you need copyNum variable.