VBA; Overflow Error 6 from For loop

Rogue909

New Member
Joined
Oct 23, 2017
Messages
11
So I have the following code....

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.

Code:
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;

Code:
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!
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rogue909

New Member
Joined
Oct 23, 2017
Messages
11
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...:oops::oops::mad::mad:

VBA doesn't like 4.3E22.

Fixed...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top