How to prevent overflow in simple VBA code?

Question

I'm new to VBA and was writing a simple code to take all numbers in column A and add 99 to those numbers in column B. However, as soon as it passes 1000, an overflow occurs. What can I do to cut off the while loop so it doesn't overflow the remaining columns with 99? Thanks!

Sub Button1_Click()

Dim n As Integer
n = 0
While Cells(1 + n, 1) <= 1000
    If Cells(1 + n, 2) = 0 Then
        Stop
    End If
    Cells(1 + n, 2).Value = Cells(1 + n, 1) + 99
    n = n + 1
Wend

End Sub

Do you know your loop never ends until reaching `Rows.Count`? With the way you code, an empty cell is returning zero so it keeps going. `Stop` does not mean exiting the loop. It simulates a BreakPoint only. FYI: [Break out of a While…Wend loop in VBA](http://stackoverflow.com/questions/12200834/break-out-of-a-while-wend-loop-in-vba) — PatricK, Mar 31 '17 at 05:49

score 0 · Answer 1 · answered Mar 31 '17 at 06:06

maybe you're after this:

Dim cell As Range

For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers) '<--| loop through column A cells with constant numeric content
    If cell.Value > 1000 Then Exit For '<--| exit loop as soon as the current cell value exceeds 1000
    cell.Offset(, 1).Value = cell.Value + 99
Next

How to prevent overflow in simple VBA code?

1 Answers1