Worksheet_Calculate() - Procedure Too Large... How to combine multiple conditions?..

Your code has given me a headache
Is Call Module_E is affecting the values each time it runs?

You could relieve pressure on the VBA by doing part of the checking within the worksheet itself
You are making VBA work very hard repeating itself
- (as far down as I looked) this seems to be in every condition

Please Login or Register  to view this content.

How about using ONE cell with the formula that would return TRUE/FALSE if all those conditions met?
- that snippet of VBA would then reduce to

Please Login or Register  to view this content.

Will that work? - or am I missing something? (it may be that there are too many subtle variations in the tests)
Are there any other similar multiple tests that could be performed by simple Excel formula?


The macro is very repetitive in general and so you could create one or two small macros and run them repetitively passing different values to them each time - this would have a huge impact

Hi,

You should also know that your error handling will only work for one error. Any further errors will not be handled as you have not used a Resume statement.

There is, in my opinion, no good way to add error handling to that. The whole code needs restructuring and refactoring.

I'm not sure what this is supposed to do

Please Login or Register  to view this content.

Do you have merged cells, or are you trying to compare one value to an array?

Looking again at your code, this would be one suggestion
- create a sub to validate the values and call it in each section

Must be placed in the same module as MainSub

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Anything that has not changed can be used again without repeating

Also range position PATTERNS may help avoid endless repeating of lines of code
- above I used the ranges as used by you, but there are relationships between consecutive range uses
- If there are patterns then it is possible to avoid repeating some of the code
eg
Set rng6 = Range("AH10") then becomes AH12 and AH14 etc so you can use
Set rng6 = rng6.Offset(2) after the first time and it will be offset by 2 more rows each time
If rows increment by 2 EVERY time then that line can be placed ONCE in MiniSub and it will increment automatically every time MiniSub is run

Time to step back and take a long hard look at everything
- can the worksheet take some of the validation burden?
- look for patterns and use them to minimise repeating code

At a very basic level you could move

Please Login or Register  to view this content.

into a separate routine called from each If block, then simply put On Error Resume Next at the start of this section and On Error Goto 0 at the end. Additionally, you seem to be repeating some of the criteria, so you should put those into a separate If clause

Please Login or Register  to view this content.

No, that won't work at all. You could use this

Please Login or Register  to view this content.

You could use
=IF(OR(AND(COUNTIF($AA$71:$AA$77,$AA$1)>0,...