Run Time Error 6 Overflow

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have a workfile which I import from our main Computer system into Excel. The macro was working perfectly file until this morning. I have bolded the macro where the problem is and have attached a sample file

When running the macro it comes up with run time error 6 overflow

It would be appreciated if you could assist

Howard

See Macro Below as well as sample file
Sub delete_Dates()
For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If IsDate(Cells(i, "B").Value) Then Cells(i, "B").ClearContents
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Right(Cells(i, 3).Value, 4)<= "2000" ThenCells(i, 3).ClearContents
End If
Next
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Cells(i, 3).Value = "12:00:00 AM" Then
Cells(i, 3).ClearContents
End If
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Right(Cells(i, 3).Value, 4) > "2020" Then
Cells(i, 3).ClearContents

End If
Next i

End Sub
nisswip.xls
ABCD
8REFTypeDateStockNo.
9
10
11
121EF08/09/20061
13BEETLE1973
14Ctl#CBOWLES,MR
15
162EF19/09/20062
17129.66
18NISSANSA
19
203EF19/09/20063
21129.66
22NISSANSA
23
244EF16/10/20064
25XTRAIL
26Ctl#CDU
Sheet1
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Howard

This commonly results from integers being used for the row reference, but the value being passed to the integer variable exceeding 32,767 (which is the max val an integer can hold). I don't see any variable declarations (ie Dim statements) in your code - is this the full code or do you have module level declarations?
 
Upvote 0
Hi Richard

Thanks for your response to my query

I have several macro's , but only two of these are coming up with run time error 6 overflow

These are as follows. I have boleded the one that are hihghlighted when the run time error occurs.

It would be appreciated if you would amend the macro to prevent the run time error occuring

Thanks

Howard

Sub delete_Dates()
For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If IsDate(Cells(i, "B").Value) Then Cells(i, "B").ClearContents
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Right(Cells(i, 3).Value, 4) <= "2000" ThenCells(i, 3).ClearContents
End If
Next
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Cells(i, 3).Value = "12:00:00 AM" Then
Cells(i, 3).ClearContents
End If
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Right(Cells(i, 3).Value, 4) > "2020" Then
Cells(i, 3).ClearContents

End If
Next i

End Sub


Sub Color_Data()
Range("B1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.NumberFormat = "dd/mm/yyy"
ActiveCell.Font.Bold = True
Columns("A:H").Interior.ColorIndex = xlNone
For MY_ROWS = 1 To Range("C65536").End(xlUp).Row
If Range("C" & MY_ROWS).Value < Range("B1").Value - 30 _
And IsDate(Range("C" & MY_ROWS).Value) Then
Range("A" & MY_ROWS).Resize(3, 7).Interior.ColorIndex = 15
End If
Next MY_ROWS
Columns("C:C").NumberFormat = "dd/mm/YYYY"
Columns("B:B").EntireColumn.AutoFit

End Sub
 
Upvote 0
Hi Howard

In your posted code I don't see any variable declaration statements (ie Dim i as Integer) and therefore I cannot see why the code would error out. The line:

If Right(Cells(i, 3).Value, 4) <= "2000" ThenCells(i, 3).ClearContents

has no space between "Then" and "Cells(" which would cause this line to error, but I am assuming this is a simple typo and that in the actual workbook code this error is not present in the code.

Have you posted the full code in the workbook?
 
Upvote 0
Hi Richard

I have included the full code (see Below). The macro was set up a few weeks ago and was running fine until this morning. Nobody else works on my files, so there is no possibility of anyone tampering with my code.

I find it very strange that my code now comes up with this run time error 6 overflow. It would be appreciated if you can assist in any way so as to overcome this error

Regards

Howard



Sub Auto_Update()

Application.ScreenUpdating = False
Import_Data
Delete_rows
delete_Dates
Color_Data
Clear_Dup_Add_Dates
Ageing
Totals

End Sub

Sub Import_Data()

Application.DisplayAlerts = False
Columns("A:Q").ClearContents
Workbooks.OpenText Filename:="C:\Extract\NISSWIP", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, OTHER:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _
, 4), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, _
1)), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Columns("C:C").Select
Selection.NumberFormat = "@"
Selection.NumberFormat = "dd/mm/yyyy"
Columns("A:A").ColumnWidth = 15.71
Columns("G:G").EntireColumn.AutoFit
Range("A1:G6").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = "NISS WIP"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1:A2").Select
Selection.Font.Bold = True
Range("A10:G10").Select
Selection.ClearContents
Range("B8").Select
ActiveCell.FormulaR1C1 = "Type"
Range("B9").Select
Selection.ClearContents
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A8:G9").Select
Selection.Font.Bold = True
Range("A1").Select
Windows("NISSWIP.xls").Activate
Columns("A:L").Select
Selection.ClearContents
Range("A1").Select
Windows("NISSWIP").Activate
Columns("A:I").Select
Selection.Copy
Windows("NISSWIP.xls").Activate
ActiveSheet.Paste
Columns("A:I").EntireColumn.AutoFit
Windows("NISSWIP").Activate
ActiveWorkbook.Close
Range("A8").Select
ActiveCell.FormulaR1C1 = "REF"
Range("C8").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C9").Select
Selection.ClearContents
Range("D8").Select
ActiveCell.FormulaR1C1 = "Stock No."
Range("D9").Select
Selection.ClearContents
Range("E8").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("E9").Select
Selection.ClearContents
Range("F9").Select
Selection.ClearContents
Range("H8").Select
Selection.Font.Bold = True
Range("I8").Select
Selection.Font.Bold = True
Range("A1").Select
End Sub




Sub Delete_rows()
For MY_ROWS = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & MY_ROWS).Value = " SS160301" Then
Rows(MY_ROWS & ":" & MY_ROWS + 9).Delete
End If
If Range("B" & MY_ROWS).Value = "---" Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
End Sub

Sub delete_Dates()
For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If IsDate(Cells(i, "B").Value) Then Cells(i, "B").ClearContents
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Right(Cells(i, 3).Value, 4) <= "2000" Then
Cells(i, 3).ClearContents
End If
Next
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow


If Cells(i, 3).Value = "12:00:00 AM" Then
Cells(i, 3).ClearContents
End If
Next i
Finalrow = Cells(65536, 3).End(xlUp).Row
For i = 1 To Finalrow
If Right(Cells(i, 3).Value, 4) > "2020" Then
Cells(i, 3).ClearContents

End If
Next i

End Sub

Sub Color_Data()
Range("B1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.NumberFormat = "dd/mm/yyy"
ActiveCell.Font.Bold = True
Columns("A:H").Interior.ColorIndex = xlNone
For MY_ROWS = 1 To Range("C65536").End(xlUp).Row
If Range("C" & MY_ROWS).Value < Range("B1").Value - 30 _
And IsDate(Range("C" & MY_ROWS).Value) Then
Range("A" & MY_ROWS).Resize(3, 7).Interior.ColorIndex = 15
End If
Next MY_ROWS
Columns("C:C").NumberFormat = "dd/mm/YYYY"
Columns("B:B").EntireColumn.AutoFit

End Sub


Sub Ageing()


Range("J8").Select
ActiveCell.Value = "Age"
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
Range("J10:J" & lr).FormulaR1C1 = "=IF(RC[-7]="""","""",IF(RC[-3]="""","""",R1C2-RC[-7]))"
Columns("J:J").NumberFormat = "#,##0"
Finalrow = Range("A65536").End(xlUp).Row
Range("A" & Rows.Count).End(xlUp).Offset(-6).Resize(9, 9).Clear


End Sub

Sub Clear_Dup_Add_Dates()
Dim lr As Long, i As Long
lr = Range("g" & Rows.Count).End(xlUp).Row
For i = 12 To lr
If Not IsEmpty(Cells(i, "g")) And Not IsEmpty(Cells(i + 1, "g")) Then
If Cells(i, "g").Value = Cells(i + 1, "g").Value Then
Cells(i + 1, "g").ClearContents
ElseIf Cells(i, "g").Value <> Cells(i + 1, "g").Value Then
Cells(i + 1, "c").Value = Cells(i, "c").Value
End If
End If
Next
End Sub


Sub Totals()
Finalrow = Range("A65536").End(xlUp).Row
Range("A" & Finalrow + 6).Value = "No. of Job Cards"
Range("B" & Finalrow + 6).Formula = "=counta(A9:A" & Finalrow & ")"
Range("A" & Finalrow + 8).Value = "Total Values"
Range("E" & Finalrow + 8).Resize(, 5).Formula = "=Sum(E9:E" & Finalrow & ")"
Range("E" & Finalrow + 8).Resize(, 5).NumberFormat = "#,##0.00;(#,##0.00)"
Range("A" & Finalrow + 10).Value = "Ageing 30 Days & Less"
Range("E" & Finalrow + 10).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 12).Value = "Ageing 31 to 60 Days"
Range("E" & Finalrow + 12).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 14).Value = "Ageing 61 to 90 Days"
Range("E" & Finalrow + 14).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&90,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 16).Value = "Ageing > 90 Days"
Range("E" & Finalrow + 16).Formula = "=Sumif(J9:J" & Finalrow & ","">""&90,E9:E" & Finalrow & ")"
Range("A" & Finalrow + 18).Value = "Total ageing"
Range("E" & Finalrow + 18).FormulaR1C1 = "=SUM(R[-8]C:R[-2]C)"
Range("A" & Finalrow + 18).Offset(, 5).FormulaR1C1 = "=R[-10]C[-1]-RC[-1]"
Range("A" & Finalrow + 18).Offset(, 5).Copy
Range("A" & Finalrow + 18).Offset(, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("E" & Finalrow + 12).Formula = "=Sumif(J9:J" & Finalrow & ",""<=""&60,E9:E" & Finalrow & ")-Sumif(J9:J" & Finalrow & ",""<=""&30,E9:E" & Finalrow & ")+F" & Finalrow + 18 & ""

Range("A" & Finalrow + 16).Offset(, 4).NumberFormat = "General"
Range("A" & Finalrow + 18).Offset(, 4).NumberFormat = "#,##0.00;(#,##.00)"
Range("A" & Finalrow + 8).Offset(, 4).Resize(20).NumberFormat = "#,##0.00;(#,##.00)"
Range("A" & Finalrow + 18).Offset(, 5).NumberFormat = ";;;"
End Sub
 
Upvote 0
Howard

The results of my analysis of the code (in the workbook you sent me) indicates the error resulted from the import format (a date format) being applied to column C of the mainframe download. There was a numerical value that could not be displayed as a date (it was too large) and hence this was causing the overflow error. I think I've fixed this simply by amending the import format to a text field (rather than numeric). I have sent you an email. Please test.
 
Upvote 0
Formatting Data

Hi Richard

I would to thank you for all yourr time and effort in resolving the run time error problem. Your assistance is most appreciated

Regards

Howard
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top