overflow

[synergy16]

can anybody tell me when the code (written in red) would cause a run time error 6....overflow? its not that big of a spreadsheet.

'***************************THIS SUB WILL POPULATE THE CURRENT PART NUMBERS AT A SPECIFIC VENDOR********************************Private Sub vendorLB_Click()
    Dim prtNumsV() As Variant
    Dim cntrV, t, y, venCounter As Variant
    Dim oPartFlag As Boolean
    
    Erase prtNumsV
    arrivedTb.Text = ""
    partNumLB.Clear
    ListBox1.Clear


'check to see if arrived parts filter box is checked
    If partFilter = True Then
        Call vendorLBn_Click
        Exit Sub
    End If


'clear box data
        serialNumTB = ""
        partQtyTB = ""
        partRevTB = ""
        lotBatchTB = ""
        jobProOrderTB = ""
        supplierTB = ""
    
    venPicked = vendorLB.Value
'get number of populated rows total
    Dim data As Range
    Dim r As Variant
    Dim k As Variant


[COLOR=#ff0000]        Set data = Sheet1.Range("c1:c" & Range("c" & Rows.Count).End(xlUp).Row)[/COLOR]
[COLOR=#ff0000]                cntrV = 0[/COLOR]
[COLOR=#ff0000]            For Each r In data[/COLOR]
[COLOR=#ff0000]                cntrV = cntrV + 1[/COLOR]
[COLOR=#ff0000]                    If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then[/COLOR]
[COLOR=#ff0000]                    End If[/COLOR]
[COLOR=#ff0000]            Next r[/COLOR]


'get number of times selected customer appears on sheet
    y = 0
    venCounter = 0
    For t = 1 To cntrV + 1
        If UCase(venPicked) = UCase(Cells(t, 9)) And UCase(Mid(Cells(t, 12), 1, 7)) <> "ARRIVED" Then
            venCounter = venCounter + 1
        End If
    Next
    
On Error Resume Next

 

[RoryA]

Do any of the cells in column C appear as ##### in the sheet?

 

[synergy16]

yes, thats just cause the column is not expanded. does that make a difference? i would think thats just a visual from formating

 

[RoryA]

Are you sure that's the cause?

 

[joeu2004]

Do the following in order to isolate the overflow to the precise statement and conditions.

1. Enter the following statement higher up -- ideally, the first executable statement:

On Error GoTo oops

2. Enter the following statements at the end of the procedure:

Exit Sub
oops:
Stop
Resume

3. Select the On Error Resume Next statement, and press f9 to set a breakpoint in order to ensure that you do not reach that point.

4. Run the procedure.

5. When you get to the Stop statement, press f8 repeatedly to execute the Resume statement and return to the offending statement.

6. At the offending statement, look at the variable values. If you need further assistance, show us the offending statement and all relevant values.

 

[synergy16]

Are you sure that's the cause?

that is where the debugger stops

 

[synergy16]

joeu,

i did your procedures and that it stops at this line

[COLOR=#FF0000] If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then[/COLOR]

 

[RoryA]

I mean are you sure it's just the column width causing the ## signs and not a cell formatted as date with a too large number in it (which would cause exactly your error)?

 

[synergy16]

I mean are you sure it's just the column width causing the ## signs and not a cell formatted as date with a too large number in it (which would cause exactly your error)?

sir, you're a scholar and a gentleman. that was exactly it. there WAS one freaking row that wasnt just a visual display due to small column width.

thanks for your help guys