+ Reply to Thread
Results 1 to 18 of 18

Code "suddenly" starts giving me error 6 overflow

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Code "suddenly" starts giving me error 6 overflow

    Hello,

    My code worked yesterday. Now it gives me an error for 'Overflow'

    This is my code snippet. The last line results in the overflow:
    Please Login or Register  to view this content.
    lastrow evaluates to 6534, as a long. rngSize in my example is b8:dp8 but can extend to row32 at times. When i hover my cursor over each part of that last statement it gives me a context tip which is correct (such as showing "range(rngSize.address)." as b8:dp8)

    As previously stated, this code worked yesterday, and i can't understand why it wouldn't today. As a note, the value of lastrow increases each time this code is run, the value being stored in a cell on the admin page. yesterday it probably ran from 6300 down to 6534 where it is at now.

    Help is appreciated, i'm a learner so you appreciate not just the answer but how I would get there for myself in future (though a straight answer isn't a bad thing by any means :P )

    TIA
    IF("helping me", "thanks", "need more detail?")

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello jayherring86,

    it is hard to tell with only a snippet of your code.

    But you could try Application.EnableEvents = False at the beginning of your Sub, and end with Application.EnableEvents = True.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello,


    I don't see what events have to do with it, but i gave the idea a try and it has no effect at all.

    thanks for the reply

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Code "suddenly" starts giving me error 6 overflow

    What line generates the error?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello,

    It's the last line in my snippet of code: "Worksheets("transactions").Range("B" & LastRow, "dp" & LastRow).Resize(rngSize.Rows.Count).Value = Range(rngSize.Address).Value"

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Code "suddenly" starts giving me error 6 overflow

    Maybe it would be a good idea if you could attach a sample workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    Well that was a fun exercise...

    I just made a sample file (i'll attach it). The sample file works just fine which indicates to me that there's nothing wrong with my code but perhaps something wrong with my workbook. Is there some sort of troubleshooting guide I can use to find what the problem might be?
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Code "suddenly" starts giving me error 6 overflow

    This line looks wrong to me

    Please Login or Register  to view this content.
    Where 2 is the start of the row and dp is column "DP"

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello jayherring86,

    Thank you for the feedback.

    Enter the VBE and Click anywhere in Module1. Press F8. Now with your mouse Test each character in every line of your code to the end. It will show inter Alia that "X" = "Nothing".

    On the line where you have ...Range(rngSize.Address).Value, the system will Show something like "With Block etc. Not set"

    Now replace the Code in your Module1, with the Code below;

    Please Login or Register  to view this content.
    Hope that helps, since it works for me.

    Regards.
    Last edited by Winon; 11-20-2016 at 03:25 AM.

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Code "suddenly" starts giving me error 6 overflow

    Hi,

    Does the following change resolve the issue?
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  11. #11
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    Quote Originally Posted by AB33 View Post
    This line looks wrong to me

    Please Login or Register  to view this content.
    Where 2 is the start of the row and dp is column "DP"
    Hello,

    The purpose of the line is to define the range at the end of the list in transactions. The line as i have it resolves to, for example, B6500:DP6500, then resizes that to the depth of rngSize (in my sample workbook i think this is about 9 rows but i don't have it open to check), then because the ranges are the same size i can "move" the data from MySheet into the transactions.

    What i'm saying is that your suggestion would render the range in transactions as "b2:dp6500", then resize to "b2:dp13" and overwrite data that already exists.

  12. #12
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    @xlnitwit changing to .value2 does indeed make the code in my real workbook work once again, and with no change to the data that is being moved. Thank you, i think i will go with this solution for now.

    @winon. I did suspect that the variable types i was using was something to do with it so i tested your solution in my real workbook (as previously mentioned, the sample workbook i uploaded actually worked flawlessly. Unfortunately there is more code that follows after what i have posted here, stuff to clear the MySheet, stuff to adjust certain pieces of information so it will be meaningful for analysis on my "transactions" over time, etc. For these further processes the variable types you suggest in your solution would need to be converted and it is causing a lot of mess. Obviously, this is not your fault and i'm actually learning a massive amount about why they are stressing the importance of clean code and it's re-usability over on my Object-Orientated Programming course!
    I thank you for your input and may well revisit and rewrite my entire routine using what you have shown me, and the knowledge from my textbooks at a later date

  13. #13
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello jayherring86,

    Please note the difference in the Dim Statements. It got rid of all "Error warnings" whilst evaluating the Code in the Module.

    Regards.

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Code "suddenly" starts giving me error 6 overflow

    Hello jayherring86,

    Our posts were at the same time, so please ignore my last post, since you picked up on the Dim Statements brilliantly!

    For these further processes the variable types you suggest in your solution would need to be converted and it is causing a lot of mess....
    Not necessarily, because you can easily Redim Statements to suit each and every Sub individually.

    Try it first, before believing it is or could be a mess.

    Kind Regards.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Code "suddenly" starts giving me error 6 overflow

    I'm glad it worked. Do you perchance have any cells that are displaying ####### in them which may contain large numbers but are formatted as Date?

  16. #16
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    Quote Originally Posted by xlnitwit View Post
    I'm glad it worked. Do you perchance have any cells that are displaying ####### in them which may contain large numbers but are formatted as Date?
    The closest thing i have is a transaction number. It is formatted as yyyymmdd followed by a 4 digit number that is incremented each time the sub runs. It is stored in Admin, cell D4 and is pulled in by my code as you can see in the OP here. This number is obviously 12 digits long and does show as ###... because the cell is not actually wide enough to display the number, though it is formatted as number and the code has been working that way since april, when the transactions was just 3 rows deep, up until it hit around 6400~ rows.
    Indeed, the first thing i checked was whether the formatting of any data was causing an issue but nothing had changed in that regard. It appears as though there is now just too much data on the transactions sheet and/or the workbook is too memory-intensive to allow enough computing power for this sub. I should mention that i'm using the workbook as an xlsb (excel binary) in order to make it easy to keep in dropbox - the file size is 1mb as compared to an xlsm version which is nearly 17mb. I am also running 32bit version not the 64 so even though all users of this workbook have computers with quad core, 8gb minimum spec, excel can only pull 4gb of ram at any once time, right?

    It's unfortunate that we run a small toy shop business and this problem has been encountered during the single busiest time of year. If it were any other time of year i'd have the time to sit and re-write the whole routine to be much cleaner but for now a quick and dirty, but working approach is the best i can hope for lol

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Code "suddenly" starts giving me error 6 overflow

    32bit Excel 2010 will only use a maximum of 2GB of RAM, but if the very same code works with Value2 and not with Value, the issue is almost certain to be a data/formatting one and not a memory issue.

  18. #18
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Code "suddenly" starts giving me error 6 overflow

    I'll bear that in mind.

    In fact, I have looked at the transaction sheet and a number of the blank cells starting at row 6437 are formatted as date, but the data that gets put in them are not dates. Could this be the problem?

    I've changed the formatting of every row in transaction to match the format of the values that will be put in those cells just to be safe but i'm still using .value2 in my sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Frequently Used Macro now Giving a" Runtime Error '1004 Application-Object Defined Error"
    By rjw524 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-02-2015, 02:39 PM
  2. How to use InStr function to search for a text that starts with "CTF" and ends with "."?
    By yoursamrit2000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2014, 06:52 PM
  3. Replies: 1
    Last Post: 08-10-2014, 01:22 PM
  4. [SOLVED] vba code to replace the column names for those column name "Proposal that starts with "1."
    By mvneema in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-31-2014, 01:21 PM
  5. [SOLVED] Runtime error "6" Overflow
    By crimzon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2014, 12:58 AM
  6. [SOLVED] emptyRow code giving Compile Error "Variable not defined"
    By colvinb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2012, 04:34 AM
  7. "Clean Me" Macro is giving "#VALUE!" error in the Notes field.
    By Ryan Watkins in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1