+ Reply to Thread
Results 1 to 5 of 5

Runtime Error 6 Overflow VBA help

  1. #1
    Registered User
    Join Date
    11-29-2018
    Location
    Bellingham
    MS-Off Ver
    2016
    Posts
    7

    Smile Runtime Error 6 Overflow VBA help

    Hello, Using this code here.

    Please Login or Register  to view this content.

    It loops through rows and pastes a formula then repastes as values. Was working when there was maybe 400 lines of data but I added 1600 and now I'm getting the error. Sheet2.Range("N1") is the location of a counta formula to find how many rows of data.

    I saw you can get around this error by declaring your variables as long but I don't know what my variables would be here.

    Any help?

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    Re: Runtime Error 6 Overflow VBA help

    I'm not sure why this would give you an overflow error. However, your description refers to a loop and there is no loop in this code. If you have other code, the root cause of the problem could be there.

    Also I think this approach is a bit convoluted. It might be more efficient to calculate the formula result in the VBA rather than copying a formula, modifying it, then converting it to a value. But it's hard to say without seeing the formula.

    I am having a hard time imagining a valid formula that has a "#" that can be converted to "C2". Also, if you are always making the same change, why don't you just put "C2" in there to begin with? Also, a formula should be assigned to the Formula property of a range, and you are assigning it to the default property, which is Value.

    It is essential to have all of your code to diagnose a problem like this. Ideally you should provide the actual file so we can also see the formulas and run the code. See yellow banner at the top of the page.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-29-2018
    Location
    Bellingham
    MS-Off Ver
    2016
    Posts
    7

    Re: Runtime Error 6 Overflow VBA help

    Hello,

    I do have other macros but shouldn't interfere with this as it doesn't reference the same sheet. I do agree that replacing # with C2 is a bit gratuitous, as I was playing around with it and this is what made it work, so I just kept it as is.

    Yeah it did seem a little funky when I made the code but it worked great up until 400 rows. I'm essentially trying to save space by applying these formulas and pasting as values. The formula the code uses looks up an account number in a large export file to get the id of the document. I'd love to share the sheets but there is a lot of PII. I can duplicate the sheet though and fill in with random values.

    This is the formula for columns D & F:
    =IF(ISERROR(INDEX('C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$B:$E,MATCH("*"&#&"*",'C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$C:$C,0),4))=FALSE,INDEX('C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$B:$E,MATCH("*"&#&"*",'C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$C:$C,0),4),IFERROR(IF(ISERROR(INDEX('C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$B:$E,MATCH("*"&SUBSTITUTE(#,"-","")&"*",'C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$C:$C,0),4))=FALSE,INDEX('C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$B:$E,MATCH("*"&SUBSTITUTE(#,"-","")&"*",'C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$C:$C,0),4),""),""))

    This is the formula for E & G:
    =IFERROR(INDEX('C:\Users\rease\Desktop\AMA Tracking\AMA Master Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$B:$E,MATCH(#,'C:\Users\rease\Desktop\AMA Tracking\Spreadsheet References\Exports\[AMA Exports.xlsx]AMAs'!$E:$E,0),1),"")

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    Re: Runtime Error 6 Overflow VBA help

    I guess I should have put this more prominently:
    It loops through rows
    The code you posted does not loop through rows. I need to see the code that loops through rows.

    Also: The "#" character in your formulas is illegal. Have you entered those formulas as text in your worksheet? Otherwise they would not be accepted.

    Also: Your code grabs data from B2, B3, B4, B5, but you only showed two formulas in your latest post. Shouldn't there be four formulas?
    Last edited by 6StringJazzer; 04-09-2021 at 03:26 PM.

  5. #5
    Registered User
    Join Date
    11-29-2018
    Location
    Bellingham
    MS-Off Ver
    2016
    Posts
    7

    Re: Runtime Error 6 Overflow VBA help

    Oh yes I understand, I'm just not familiar with the semantics of vba coding. Rather than looping it just pastes the formulas down each row then re copies and pastes as values.

    The "#" is allowed in the cell as it is formatted as text rather than a formula with an apostrophe in front ('=INDEX etc.)

    You are correct there are 4 formulas. The formulas in B2 & B4 are the same with the only difference being the index match search locations are different sheets in the same workbook. Formulas in B3 & B5 are the same except the index match search locations as well, but also uses either the value in column D or column F as the reference. To clarify, the formulas in B2 & B4 search that database via account number (which is the C2 reference) and return the ID of that document. The formulas in B3 & B5 use that Document ID as the reference to pull the date.

    I hope this doesnt confuse more but I'll try to upload a sheet with no PII here shortly.

    I also tried removing the "#" in each formula and replacing with either C2, D2, or F2. Same overflow error.
    Please Login or Register  to view this content.

+ 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] Overflow Runtime Error 6
    By xjohnson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2018, 09:22 AM
  2. [SOLVED] Runtime error 6: Overflow
    By jeetkapadia in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-04-2017, 05:40 PM
  3. [SOLVED] VBA runtime error '6': Overflow
    By clapforthewolfman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-11-2013, 05:07 PM
  4. [SOLVED] Overflow Runtime error 6
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2013, 12:23 PM
  5. runtime error 6: overflow?
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2007, 06:36 AM
  6. runtime error '6' overflow
    By don in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2005, 10:05 PM
  7. [SOLVED] Runtime Error 6 Overflow
    By Wescotte in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-13-2005, 07:05 PM

Tags for this Thread

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