Excel VBA - check if value is type DOUBLE

Question

I have a column of numbers and I am using VBA to divide them by 1000. However, I only want to do this if there's NO decimal in there. I.e. numbers like 10093, 20398, 2039348, 298, 299, 10 will all be divided by 1000, but 2938.39, 2883.2, .2 won't.

I am thinking just to create a loop that checks if the cell value is a DOUBLE and if so, skip it - otherwise, divide.

So, I thought this might work, but it doesn't:

For Each cel In importWS.Range(Cells(2, 2), Cells(dataLastRow - datafirstrow + 1, 2))
    If Not CDbl(cel.Value) Then
        cel.Value = cel.Value / 1000
    End If
Next cel

Any ideas?

Answer 1

Alternatively, check for integer:

With cel
    .Value = IIF(CLng(.Value)=.Value, .Value / 1000, .Value)
End With

This says: if it's an integer/long (no decimal) then do the division by 1000, otherwise use the original cel.Value.

Note: this will not work for extraordinarily large integers (anything larger than a Long data type will raise an overflow error).