Solved

Excel 2010 Run Time error '6' Overflow

Posted on 2012-03-13
14
228 Views
Last Modified: 2014-11-25
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
0
Comment
Question by:CRHIV
  • 6
  • 5
14 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37715140
Can't find a procedure called FG. Which procedure and which line does it fail on?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37715341
Is the problem sub Post?
0
 

Author Comment

by:CRHIV
ID: 37715886
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37716125
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.
0
 

Author Comment

by:CRHIV
ID: 37716384
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.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 37716419
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.
0
 

Author Comment

by:CRHIV
ID: 37720574
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
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 37720959
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.
0
 

Author Comment

by:CRHIV
ID: 37721480
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
0
 

Author Comment

by:CRHIV
ID: 37721491
And I removed the data from the bottom of the Past History sheet.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 37721565
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.
0
 

Author Comment

by:CRHIV
ID: 37721826
Thanks for your help.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question