Excel VBA "OVERFLOW" Error

powera86 · Apr 23rd 2012

Hi there,

Would anyone be able to tell me why everytime I execute this bit of code I get an overflow error?

When I debug it always shows the value of i being 1 more than the starting row value. Ie. if inital row is 2, then the value of i at the point of error in the code is i=3.

Thanks,

Code

Sub pastevalues()


Dim C As Range
Dim i As Long
Dim diag As New ProgressDialogue
diag.Configure "Executing Code", "Executing Code...", Range("B2").Row, Range("B262144").Row
diag.Show


For Each C In Range("b2:b262144").Cells
For i = Range("B2").Row To Range("B262144").Row
    diag.SetValue i
    diag.SetStatus "Executing Code..." & i
    If diag.cancelIsPressed Then Exit For
Next i


            C.value = C.value
        Next
    
diag.Hide
MsgBox "Process Complete"


End Sub

Display More

jproffer · Apr 23rd 2012

Re: Excel VBA "OVERFLOW" Error

I can't test it as is because of the ProgressDialogue, but here's a shot. All I did was remove some of the unnecessary code (Range.Row to Range.Row, for instance...you can just use numbers). May still bug out, but who knows. If that doesn't fix it (and it probably wont) could you post your User Defined Type coding for the ProgressDialogue so we can test properly.

Code

Sub pastevalues()
     
    Dim C As Range
    Dim i As Long
    Dim diag As New ProgressDialogue
    diag.Configure "Executing Code", "Executing Code...", 2, 262144
    diag.Show
     
    For Each C In Range("b2:b262144")
        For i = 2 To 262144
            diag.SetValue i
            diag.SetStatus "Executing Code..." & i
            If diag.cancelIsPressed Then Exit For
        Next i
         
        C.Value = C.Value
    Next
     
    diag.Hide
    MsgBox "Process Complete"
     
End Sub

Display More

AAE · Apr 24th 2012

Re: Excel VBA "OVERFLOW" Error

I can't image why you would want to loop cell-by-cell to convert to values.

You could do this in one fell swoop using a copy/pastespecial - however, that being said, there is usually more to the story than what is posted.

Convert Formulas to Values Instantly... For the entire workbook!

Excel VBA "OVERFLOW" Error

Participate now!

Share