VBA; Overflow Error 6 from For loop

[Rogue909]

So I have the following code....

Sub CalibrationCopy()

'CalibrationCopy imports the calibration data that is relavent to the current setup.


' Call in Variables
    Dim CalibSh As Worksheet
    Dim i As Long
    Dim LastRow As Long
    Dim CalibDate As Long
    
    
    
'Import Data
'Clibration Directory
 CalibDirect = Sheets("Under the hood").Range("AJ4").Value
 'Calibration File Name
 CalibName = Sheets("Under the hood").Range("AJ6").Value
 'Calibration Sheet Name
 CalibSheet = Sheets("Under the hood").Range("AJ8").Value
 'Day of interest
 CalibDate = Sheets("Under the hood").Range("A3").Value
 
 Application.ScreenUpdating = False
  
 'Set directory & filename... Open workbook to pull data from
    Directory = CalibDirect
    Filename = Dir(Directory & CalibName)
    Workbooks.Open (Directory & Filename)
   
 'Set the workbooks & worksheets of interest to variables
    Set CalibSh = ThisWorkbook.Worksheets("Calibration Data")
    Set CalibSource = Workbooks(CalibName).Sheets(CalibSheet)
    
 'Clear previous data
    CalibSh.UsedRange.ClearContents
   
 'Determine the last row of the source file and set that to a variable
 CalibSource.UsedRange
 LastRow = CalibSource.UsedRange.Rows(CalibSource.UsedRange.Rows.Count).Row
 
 'Cycle using i to cycle through worksheet until the last row. Copy and paste any matching entries.
 For i = 1 To LastRow
     If CalibSource.Cells(i, 1).Value = CalibDate 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(CalibName).Close False
    Sheets("Data Entry").Activate
    
Application.ScreenUpdating = True


End Sub

The goal of this is to import calibration data from a particular day (all of the dates are stored in the A column.) It does this by opening the calibration log and going through to check by day. I've been getting an error; overflow 6. I included message boxes to try and isolate & identify the error. I've found it's something to do with the For loop.

For i = 1 To LastRow     
      If CalibSource.Cells(i, 1).Value = CalibDate 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

In particular I think it exists in the header;

If CalibSource.Cells(i, 1).Value = CalibDate Then

But I can't figure out what's wrong?

I had this buttoned up and working earlier. IT upgraded my computer (thank you!) and now I have office 2016 (had 2010). Could this be part of the issue?

Thoughts?

Thank you!

 

[Rogue909]

Fixed...

Guys on the floor (putting stuff into calibration sheet) went and copied an old calibration sheet. I've already had to fix this before.

Basically; in calibration sheet there was a cell in A column that had a fictitious value. something 4.3E22 that was entered in several years ago. I've already deleted this once...

VBA doesn't like 4.3E22.

Fixed...