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

**Ourkid123uk** · 03-21-2018, 12:33 PM

Hi,

I have the following code as part of more code within "Sheet1" Module using the Worksheet_Calculate() command.

30:
On Error GoTo 31

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH10") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G9") >= Range("AM5") And Range("G9") <= Range("AJ6") And Range("AD10") < Range("AC6") _
Or Range("O9") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH10") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G9") >= Range("AM5") And Range("G9") <= Range("AJ6") And Range("AD10") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True


End If


31:
On Error GoTo 32

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH12") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G11") >= Range("AM5") And Range("G11") <= Range("AJ6") And Range("AD12") < Range("AC6") _
Or Range("O11") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH12") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G11") >= Range("AM5") And Range("G11") <= Range("AJ6") And Range("AD12") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



32:
On Error GoTo 33

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH14") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G13") >= Range("AM5") And Range("G13") <= Range("AJ6") And Range("AD14") < Range("AC6") _
Or Range("O13") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH14") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G13") >= Range("AM5") And Range("G13") <= Range("AJ6") And Range("AD14") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



33:
On Error GoTo 34

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH16") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G15") >= Range("AM5") And Range("G15") <= Range("AJ6") And Range("AD16") < Range("AC6") _
Or Range("O15") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH16") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G15") >= Range("AM5") And Range("G15") <= Range("AJ6") And Range("AD16") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



34:
On Error GoTo 35

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH18") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G17") >= Range("AM5") And Range("G17") <= Range("AJ6") And Range("AD18") < Range("AC6") _
Or Range("O17") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH18") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G17") >= Range("AM5") And Range("G17") <= Range("AJ6") And Range("AD18") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



35:
On Error GoTo 36

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH20") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G19") >= Range("AM5") And Range("G19") <= Range("AJ6") And Range("AD20") < Range("AC6") _
Or Range("O19") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH20") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G19") >= Range("AM5") And Range("G19") <= Range("AJ6") And Range("AD20") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



36:
On Error GoTo 37

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH22") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G21") >= Range("AM5") And Range("G21") <= Range("AJ6") And Range("AD22") < Range("AC6") _
Or Range("O21") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH22") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G21") >= Range("AM5") And Range("G21") <= Range("AJ6") And Range("AD22") < Range("AC6") Then



Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If


37:
On Error GoTo 38

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH24") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G23") >= Range("AM5") And Range("G23") <= Range("AJ6") And Range("AD24") < Range("AC6") _
Or Range("O23") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH24") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G23") >= Range("AM5") And Range("G23") <= Range("AJ6") And Range("AD24") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If


38:
On Error GoTo 39

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH26") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G25") >= Range("AM5") And Range("G25") <= Range("AJ6") And Range("AD26") < Range("AC6") _
Or Range("O25") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH26") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G25") >= Range("AM5") And Range("G25") <= Range("AJ6") And Range("AD26") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If


39:
On Error GoTo 40

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH28") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G27") >= Range("AM5") And Range("G27") <= Range("AJ6") And Range("AD28") < Range("AC6") _
Or Range("O27") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH28") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G27") >= Range("AM5") And Range("G27") <= Range("AJ6") And Range("AD28") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If


40:
On Error GoTo 41

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH30") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G29") >= Range("AM5") And Range("G29") <= Range("AJ6") And Range("AD30") < Range("AC6") _
Or Range("O29") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH30") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G29") >= Range("AM5") And Range("G29") <= Range("AJ6") And Range("AD30") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

End If



41:
On Error GoTo 42

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH32") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G31") >= Range("AM5") And Range("G31") <= Range("AJ6") And Range("AD32") < Range("AC6") _
Or Range("O31") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH32") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G31") >= Range("AM5") And Range("G31") <= Range("AJ6") And Range("AD32") < Range("AC6") Then




Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True


End If

Because i have so much code within "Sheet1" its returning "Procedure Too Large" compile error.

I have already optimised as best i can calling for Macros in separate Modules but still need to condense this.

I'm sure there is someway to combine into ONE multiple IF , AND, OR statement, im just not advanced enough to know how!

Andy help greatly appreciated guys!

**kev_** · 03-21-2018, 01:07 PM

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

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3")

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

IF Range("XX999") =TRUE...

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

**xlnitwit** · 03-21-2018, 01:45 PM

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.

**Ourkid123uk** · 03-21-2018, 01:59 PM

Sorry guys! Basically im self taught for less than a year and i guess im coding a way which my mind works (not necessarily the best way at all!)

Ill try that now in a cell and check that in VBA, good idea!

Also,

Where do i put the 'Resume' Statement to get my error handler to work? Basically i have more code on that sheet! (i know!) and it goes through all the conditions and if error goes to next set conditions at the end it goes back to the top. I do this as the cells its checking are constantly updating from an API data source.

Thanks for your help guys!

**xlnitwit** · 03-21-2018, 02:04 PM

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

If Range("AA1") = Range("AA71:AA77")

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

**Ourkid123uk** · 03-22-2018, 06:45 AM

Ahhh! you guys are right i need to completely re-structure... i'm learning a lot though so thank you!

For;

If Range("AA1") = Range("AA71:AA77")

I'm trying to ask - IF Cell AA1 is in ANY of the Cells AA71:AA77 as a condition. Is this not right as this is vital at the start of my IF statement?

**kev_** · 03-21-2018, 02:09 PM

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

At top of the module above all procedures - declare all your variables - makes them available to all procedures in this module
Dim rng 1 as range, rng2 as range etc

Private Sub MiniSub()
If rng1 = rng2 And rng3 = "In-play" And rng4 >= rng5 And rng6 = rng7 And _
rng8 = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And rng9 >= rng10 And rng11 <= rng12 And rng13 < rng14 _
Or ....
etc
End Sub

Sub MainSub()
30:
On Error GoTo 31

set rng1 =Range("AA1")
set rng2= Range("AA71:AA77")
set rng3 =Range("G1")
etc
Call MiniSub
Application.EnableEvents = False
Call Module_E

Application.EnableEvents = True
31:
On Error GoTo 32

any ranges that are the same do not need re-specifying (eg rng1,rng2,rng3..)
Set ONLY ranges that differ
Call MiniSub
Application.EnableEvents = False
Call Module_E
Application.EnableEvents = True

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

**xlnitwit** · 03-21-2018, 02:20 PM

At a very basic level you could move

Application.EnableEvents = False

Call Module_E

Application.EnableEvents = True

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

If Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") Then

   On Error Resume Next

   If Range("AH10") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G9") >= Range("AM5") And Range("G9") <= Range("AJ6") And Range("AD10") < Range("AC6") _
Or Range("O9") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH10") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G9") >= Range("AM5") And Range("G9") <= Range("AJ6") And Range("AD10") < Range("AC6") Then

      Call New_Module_E_Routine

   End If

   If Range("AH12") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G11") >= Range("AM5") And Range("G11") <= Range("AJ6") And Range("AD12") < Range("AC6") _
Or Range("O11") = "PLACED" And Range("AA1") = Range("AA71:AA77") And Range("G1") = "In-play" And Range("H4") >= Range("Z3") And Range("AH12") = Range("AE4") And _
Range("AI5") = "Use Projected Stamped SP (Specified Time Stamped By Cell 'R5')" And Range("G11") >= Range("AM5") And Range("G11") <= Range("AJ6") And Range("AD12") < Range("AC6") Then

      Call New_Module_E_Routine

   End If
'etc
End If

**xlnitwit** · 03-22-2018, 06:48 AM

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

If Not IsError(Application.Match(Range("AA1"), Range("AA71:AA77"), 0)) Then

**Ourkid123uk** · 03-22-2018, 06:57 AM

Yes that works thank you!

This is what i have now in a helper column (as suggested);

=IF(OR(AND($AA$1=$AA$71:$AA$77,$G$1="In-play",$H$4>=$Z$3,AH10=$AE$4,$AI$5="Use Actual SP",G9>=$AK$5,G9<=$AJ$6,AD10<$AC$6),AND(O9="PLACED",$AA$1=$AA$71:$AA$77,$G$1="In-play",$H$4>=$Z$3,AH10=$AE$4,$AI$5="Use Actual SP",G9>=$AK$5,G9<=$AJ$6,AD10<$AC$6)),"TRUE","-")

You can see that i still have the code that wont work in there. Using your IsError method, how can i combine that formula with mine?

**xlnitwit** · 03-22-2018, 06:58 AM

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

**Ourkid123uk** · 03-24-2018, 08:24 AM

Sorry for late reply!

Perfect... as always xlnitwit (Rep Left

) Thank you!

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Issue with LARGE, MATCH, INDEX and duplicates, multiple conditions

[SOLVED] How to combine multiple worksheets of text data into one large worksheet?

large function multiple conditions

LARGE Function with multiple conditions

Combine data from multiple sheets with conditions

Combine multiple INDEX/Match conditions

Bookmarks

Bookmarks

Posting Permissions