Replacement VBA for sumif on a large set of data - taking too long to calcuate

[elleb]

Hi all,

I have a VBA code that runs a lot of formulas and variables over a large set of data (over 50k rows). When I have gone to run the formula it is taking around 10minutes to complete each time, through process of elimination (commenting out segments of coding bits at a time) I have deduced that it is the following code that is slowing it down which uses sumif formulas.

Please Login or Register  to view this content.

I have searched through the forum and have found some helpful threads which replace the sumif formulas with different coding (scripting dictionaries etc) but, my VBA knowledge is intermediate at best and I am struggling to understand the coding, I wondered if anyone could help me implement this into my code to speed the VBA and whilst explaining the coding at the same time so I know what it is doing for future reference?

TIA

[Eastw00d]

Maybe this makes a little difference:

Please Login or Register  to view this content.

Cheers
Erwin

[jolivanes]

I assume you are a little mixed up
ActiveCell.FormulaR1C1 = "=SUMIF(C18,RC19,C11)"
C18 is Row 18 and Column 3
RC19 is Row 19 and Column 471
C11 is Row 11 and Column 3

I am nearly 100 percent convinced that you don't mean to reference the 471st Column.

En Erwin zit op het Havermarktje en had dat nog niet in de gaten.

[Fluff13]

Those are R1C1 references, not A1 references, so C18 is column R and RC19 is column S on the same row as the formula.

[jolivanes]

I thought that you could not mix R1C1 references with A1 references. I also got mixed up with it being a formula, no double quotes.
My apologies to Erwin also. I "accused" him of having a beer at the entertainment center in his home town.

[Eastw00d]

Unfortunately the bars on the Havermarkt are closed in these days ....
besides that, I was using only the formulas of the OP, and knew that these were relative.

Cheers
Erwin

[jindon]

elleb,

Only guess from your code.
If you upload a small set of data and the result, I can test it.

Please attach a sample workbook (not a picture or pasted copy). 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.

Please Login or Register  to view this content.

[elleb]

Hi all,

Thanks for the responses, I am having electrical/connectivity issues at the moment so cannot get onto the laptop to test.

As soon as I can get back online I'll get back to you all 😁

Thank you again

[elleb]

Hi all,

I've managed to get online and test the last coding, it now takes seconds to complete rather than minutes so I really appreciate all the help you've provided.

Jindon, this might not be possible but, would you be able to explain the coding to me a little so that I can learn/understand it as I need to add another two columns (they will be in V2 and with similar formulas and I am not sure how to include them in the VBA. I'd really appreciate it as its an excellent piece of code!

[jindon]

Jindon, this might not be possible but, would you be able to explain the coding to me a little so that I can learn/understand it as I need to add another two columns (they will be in V2 and with similar formulas and I am not sure how to include them in the VBA. I'd really appreciate it as its an excellent piece of code!

As I mentioned, I just coded based on your code, so I don't fully understand the data structure etc.
If you upload a small sample workbook with before/after, it would help.