Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2010 Run Time error '6' Overflow

Posted on 2012-03-13
14
Medium Priority
?
238 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
12 Comments
 
LVL 77

Expert Comment

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

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Accepted Solution

by:
Norie earned 2000 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 36

Assisted Solution

by:Norie
Norie earned 2000 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 36

Assisted Solution

by:Norie
Norie earned 2000 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 36

Assisted Solution

by:Norie
Norie earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

581 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