CODEWITHSUNDEEP
vbaexcel
BruceWayne
BruceWayne

Reputation: 23283

Excel VBA - check if value is type DOUBLE

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?

Upvotes: 2

Views: 26559

Answers (3)

Absinthe
Absinthe

Reputation: 3391

Look for a full stop:

If IsNumeric(cellValue) And InStr(1, cellValue, ".") > 0 then isDouble = true

Upvotes: 3

David Zemens
David Zemens

Reputation: 53623

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).

Upvotes: 5

Sobigen
Sobigen

Reputation: 2169

CDbl converts the value to a double.

Try

if TypeName(cel.value) = "Double" then
    cel.value = cel.value / 1000
end if

Upvotes: 5

Related Questions