We help IT Professionals succeed at work.

Excel 2010 Run Time error '6' Overflow

On the FG program, when a sheet is sent to post, it adjusts inventory for each item, but when it records the items into the "Past History" sheet, I get the error message. It puts a "P" beside the "POST" button on the right side of the page. Any protected sheets use "CHUCK" as the password. The posting has been working until today. Can you spot the problem?

Thanks,

Chuck
FG.xls
Comment
Watch Question

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Can't find a procedure called FG. Which procedure and which line does it fail on?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Is the problem sub Post?

Author

Commented:
In the FG.xls, there are sheets 1-20 that orders are taken on. After the order is shipped, the "POST" button is pressed to update the inventory, then the items are added to the "PAST HISTORY" sheet as a record. The program then puts a "P" beside the "POST" button, so the user and the program knows it has been done. Then the user presses the "POST" button again. If the program sees the "P" beside the button, it asks the user if they want to clear the sheet for reuse. The error has just shown up today. I read where the "xlastrow" variable needed to be set to "Long", not "Integer". I tried that, but doesn't seem to work. I am resending the FG.xls.
FG.xls
Analyst Assistant
CERTIFIED EXPERT
Commented:
Which line of code is highlughted when the error happens?

If you can't tell us that can you tell us how to recreate the error?

Actually I found the line of code that's causing the problem
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row

Open in new window

I also found this
    Dim xlastrow As Integer

Open in new window


When I change that to this the code doesn't error anymore.
    Dim xlastrow As Long

Open in new window

I also noticed something else.

You have this.

   z = Sheets("INVENTORY").Range("A65536").End(xlUp).row

Open in new window


But you've declared z as String.
Dim z As String

Open in new window

I think you should also declare that as Long.

Author

Commented:
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row
Is the line that it has the hang up on. When I change "Dim xlastrow as long", I don't get an error, but it doesn't put the items into PAST HISTORY.
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
That's a different issue really.

If you make the change to Long you can see what's happening in the code.

You can do that by settting a break-point on this line with F9,
xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row

Open in new window

then step through with F8.

Author

Commented:
How come when I change  " Range("A65536") " to " Range("A65000") ", on the line below, it works? Even without changing "Dim xlastrow as Integer" to "Dim xlastrow as Long"

xlastrow = Sheets("Past History").Range("A65536").End(xlUp).row
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
It's because you have data in rows 65468 to 65474.

So if you start at row 65000 and go up the next row with data is 8356, which can be stored as an Integer.

If you start at 65535 and go up the next row with data is 65474, which can be stored in and Integer.

I'm pretty sure that data shouldn't be there and it could actually explain why it appears items aren't getting put in the past history.

Any new data will be put below those rows.

I don't know how or when those rows got there but they should be removed, or moved up to join the other data.

You should still switch to using Long for xlastrow.

PS Instead of using 65536 in the code you can use Rows.Count. That will return the no of rows in a sheet and could help avoid typos.

Author

Commented:
Exactly how would I phrase the xlastrow statement to find the place to start adding data?
The line below returns xlastrow as 65534, and starts from there.

xlastrow = Sheets("Past History") . UsedRange.Rows.Count

Author

Commented:
And I removed the data from the bottom of the Past History sheet.
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
You can use the code you originally have, just declare xlastrow as Long.

You don't need to use Rows.Count, but it can be helpful.

If you were to use it the code would look like this.
xlastrow = Sheets("Past History").Range("A" & Rows.Count).End(xlUp).Row

Open in new window


The important things are to delete/move that misplaced data and declare the variable as Long.

Author

Commented:
Thanks for your help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.