Solved

Excel 2010 Run Time error '6' Overflow

Posted on 2012-03-13
14
222 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now