CODEWITHSUNDEEP
vba
Black Box
Black Box

Reputation: 11

Do While - Overflow error

This code looks for the column with header "Quantity Dispensed," then convert the strings in the column by treating the right three digits as decimals, e.i. 00009102" = 9.102

Sub ConvertDec()
Dim colNum As Integer
Dim i As Integer
Dim x As Integer

colNum = WorksheetFunction.Match("Quantity Dispensed", ActiveWorkbook.ActiveSheet.Range("1:1"), 0)
i = 2

Do While ActiveWorkbook.ActiveSheet.Cells(i, colNum).Value <> ""
    x = Evaluate(Cells(i, colNum).Value)
    Cells(i, colNum) = Int(x / 1000) + (x Mod 1000) / 1000
    i = i + 1
Loop

End Sub

I'm getting Overflow error on the line "x = Evaluate..." while executing.

The values in the column are in string form. e.g. "0000120000".

Upvotes: 1

Views: 571

Answers (1)

RubberDuck
RubberDuck

Reputation: 12768

120000 is greater than the maximum value of integer 32768. Use the Long type instead.

Simoco

Upvotes: 1

Related Questions