Runtime Error 11: Divison by Zero

[slven]

I have a simple macro to loop through worksheets in a workbook and look for the last/largest ID number (Column A) in the entire workbook. This code works periodically when running and always works when stepping through in debug mode. BUT other times when running it (even in the same workbook), i get a Runtime Error 11 Division by Zero and for life of me Can't figure out why. There is no division by zero. And it seems to happen on different worksheets too before it fails.

The code keeps failing at the line in bold below:

Sub NextNumber()
'
' NextNumber Macro
' finds next number in the workbook
'
' Keyboard Shortcut: Option+Cmd+Shift+N
'

Dim vMax As Integer
Dim holder As Integer
Dim x As Integer
Dim y As Integer 'loop through worksheets
Dim ws_count As Integer 'count number of worksheets
Dim sh As Integer

sh = ActiveSheet.Index

vMax = 1
y = 1
x = 1
ws_count = ThisWorkbook.Worksheets.Count

vMax = Application.WorksheetFunction _
.Max(Range("A:A"))

Do While y <= ws_count
ActiveWorkbook.Sheets.Activate
holder = Application.WorksheetFunction.Max(Range("A:A"))

If IsNumeric(holder) = True And (holder < 10000) Then
If holder > vMax Then
vMax = holder
End If
End If

y = y + 1
Loop

MsgBox vMax + 1, , "Next Number"


End Sub

 

[bfreescott]

are there any ID's in column A larger than 2147483647