Error 6: Overflow; Dim as long already?

[Rogue909]

So I have a with loop....

Dim i As Long

 For i = 1 To 4000        If CalibSource.Cells(i, 1).Value = "1/2/2013" Then
            CalibSource.Range(CalibSource.Cells(i, 1), CalibSource.Cells(i, 15)).Copy
            CalibSh.Range("A" & CalibSh.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i

If I change the "For i=1 to 4000" to 5000 (to increase the search range) than I immediately get an error 6 overflow.

My research has indicated that this is because i is not called as an integer... however it clearly is?

What is it that I am missing? Unfortunately I need to have a search range larger than 4000 rows so I need to figure this out.

 

[RoryA]

Which line actually gives the overflow? It shouldn't be the For line based on what you posted.

 

[Rogue909]

Which line actually gives the overflow? It shouldn't be the For line based on what you posted.

I'm not sure? How do I tell it to indicate which line?

When I run all I get is a pop-up with error 6.

I have commented out the copy and paste lines (as follows) and I get the same error.

    For i = 1 To 5000        If CalibSource.Cells(i, 1).Value = "1/2/2013" Then
          '  CalibSource.Range(CalibSource.Cells(i, 1), CalibSource.Cells(i, 15)).Copy
           ' CalibSh.Range("A" & CalibSh.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i

To give some insight... my whole code is as follows (maybe you can see something obvious)

Sub SpecialCopy()

' Call in Variables
    Dim CalibSh As Worksheet
    Dim i As Long
    
 'Set directory & filename... Open workbook to pull data from
    directory = "E:\Me\QA Experimental Forms\"
    fileName = Dir(directory & "QA Daily Calibration.xlsx")
    Workbooks.Open (directory & fileName)
   
 'Set the workbooks & worksheets of interest to variables
    Set CalibSh = ThisWorkbook.Worksheets("Calibration Data")
    Set CalibSource = Workbooks("QA Daily Calibration").Sheets("Current Year")
    
 'Clear previous data
    CalibSh.UsedRange.ClearContents
   
 'Cycle using i to cycle through worksheet and find calibration entries that match that equivalent to the imported date. Copy the row and paste it into the calibration data of the main sheet.
    For i = 1 To 5000
        If CalibSource.Cells(i, 1).Value = "1/2/2013" Then
          '  CalibSource.Range(CalibSource.Cells(i, 1), CalibSource.Cells(i, 15)).Copy
           ' CalibSh.Range("A" & CalibSh.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
    
 'Close excess workbook
    Workbooks("QA Daily Calibration").Close False




End Sub

Be easy on me with the commenting. I'm still learning and it's a rough draft.

 

[RoryA]

Do you not get a debug button? If so, what line is highlighted when you press it?

 

[Rogue909]

No?

I click run (F5) from the VBA interface and I get the Error 6 Overflow. My only 2 options are "OK" and "Help".
No highlighting occurs. I've seen the highlighted lines before when I've entered something wrong; It's a little yellow arrow indicating the line with the problem. But this time I get nothing.

If I run it from the excel sheet I just get an Overflow error with "OK" and "Help". It doesn't tell me Error 6 then.

 

[RoryA]

If you change this line:

If CalibSource.Cells(i, 1).Value = "1/2/2013" Then

to this:

If CalibSource.Cells(i, 1).Value2 = "1/2/2013" Then

does the code then work?

 

[Rogue909]

Yes/No

The error clears but then the code doesn't seem to work?

Obviously I'm still in testing phase (hence static reference to the date). My calibration data for that date is early in the worksheet... row 5, 6, and 7.

If the for loop is set to 4000 it will return the data applicable to that day. If I change it to value2 and put the for loop to 5000 it will no longer return the data but it doesn't error.

What is value2?

 

[Joe4]

You can add some error handling to find out which value of i is causing issues, like this:

Sub SpecialCopy()

' Call in Variables
    Dim CalibSh As Worksheet
    Dim i As Long
    
    On Error GoTo err_chk
    
 'Set directory & filename... Open workbook to pull data from
    directory = "E:\Me\QA Experimental Forms\"
    Filename = Dir(directory & "QA Daily Calibration.xlsx")
    Workbooks.Open (directory & Filename)
   
 'Set the workbooks & worksheets of interest to variables
    Set CalibSh = ThisWorkbook.Worksheets("Calibration Data")
    Set CalibSource = Workbooks("QA Daily Calibration").Sheets("Current Year")
    
 'Clear previous data
    CalibSh.UsedRange.ClearContents
   
 'Cycle using i to cycle through worksheet and find calibration entries that match that equivalent to the imported date. Copy the row and paste it into the calibration data of the main sheet.
    For i = 1 To 5000
        If CalibSource.Cells(i, 1).Value = "1/2/2013" Then
          '  CalibSource.Range(CalibSource.Cells(i, 1), CalibSource.Cells(i, 15)).Copy
           ' CalibSh.Range("A" & CalibSh.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
    
    On Error GoTo 0
    
 'Close excess workbook
    Workbooks("QA Daily Calibration").Close False

    Exit Sub
    

err_chk:
    MsgBox "Error number: " & Err.Number & vbCrLf & _
            "Error description: " & Err.Description & vbCrLf & _
            "Value of i: " & i
            
End Sub

I would check out that cell (column A for that row number). Anything odd about it (locked, merged, hidden, etc)?

 

[RoryA]

Since that removed the error, you almost certainly have a cell in that column formatted as a Date that contains a large number. It would appear in the sheet as ########## so should be easy to spot. It's also obviously somewhere between rows 4001 and 5000.

By the way, that's not a good way to compare dates - you should use a date value, for example:

If CalibSource.Cells(i, 1).Value2 = DateSerial(2013, 1, 2) Then

 

[Rogue909]

Since that removed the error, you almost certainly have a cell in that column formatted as a Date that contains a large number. It would appear in the sheet as ########## so should be easy to spot. It's also obviously somewhere between rows 4001 and 5000.

That was it exactly!!!

Row 4377 had some erroneous value in the A column. No Earthly idea why but it was there.

By the way, that's not a good way to compare dates - you should use a date value, for example:

If CalibSource.Cells(i, 1).Value2 = DateSerial(2013, 1, 2) Then

How can I implement this when I reference a cell? Can I do DateSerial(Sheets("BlahBlah").Range("A1").Value)?