1

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

Improve this question
7
  • 3
    try to change Dim x As Integer to Dim x As Long. max value of integer is only 32768. Also it'd be more reliable to use Dim i As Long. Btw, is there any reason to use x = Evaluate(Cells(i, colNum).Value) instead x = Cells(i, colNum).Value? – Dmitry Pavliv Apr 1 '14 at 20:10
  • The values in the column are in string form e.g. "0000120000" – Black Box Apr 1 '14 at 20:17
  • 1
    120000 greater than max value of integer 32768. Use Long type instead. And there is no need to use Evaluate – Dmitry Pavliv Apr 1 '14 at 20:21
  • That seemed to be the problem. I read that Integer holds up to 2,147,483,647, but the max value you mentioned is clearly correct. – Black Box Apr 1 '14 at 20:24
  • 1
    it's in VB.NET max value of integer 2147483647, in VBA - 32768 – Dmitry Pavliv Apr 1 '14 at 20:26
1

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

Simoco

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.