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?
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.
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),"")
I guess I should have put this more prominently:
The code you posted does not loop through rows. I need to see the code that loops through rows.It 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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks