PDA

View Full Version : Compressing long IF OR AND functions



RINCONPAUL
10-16-2015, 12:31 PM
I'm after some help to compress this long IF OR AND function:
IF A1 = 7 OR 9 OR 11 OR 12 OR 14
AND B1<>1
AND C1<>4,6,8,9
AND D1 <>3,8
AND E1<>4,7,8
AND F1<>4
AND G1<>1,3,4,5,6,7,8,9,10,12,14,19,20,22,28,35
AND H1<>10,12,13,15
AND I1<>4,15,17,18,19,20
"YES","NO"

Cheers

SamT
10-16-2015, 01:59 PM
I take it that the commas in the later lines represent "OR"s.

Are those the Actual values to test for?

Replace that long function with

=CheckAee2Eye($A1:$I1)

Place this code in a standard Module.

Option Explicit

Public Function CheckAee2Eye(RngAI As Range)

Select Case RngAI.Cells(1)
Case Is = 7, 9, 11, 12, 14
Case Else
CheckAee2Eye = "NO"
Exit Function
End Select

If RngAI.Cells(2) = 1 Then
CheckAee2Eye = "NO"
Exit Function
End If

Select Case RngAI.Cells(3)
Case Is = 4, 6, 8, 9
CheckAee2Eye = "NO"
Exit Function
End Select

If RngAI.Cells(4) = 3 Or RngAI.Cells(4) = 8 Then
CheckAee2Eye = "NO"
Exit Function
End If

Select Case RngAI.Cells(5)
Case Is = 4, 7, 8
CheckAee2Eye = "NO"
Exit Function
End Select

If RngAI.Cells(6) = 4 Then
CheckAee2Eye = "NO"
Exit Function
End If

Select Case RngAI.Cells(7)
Case Is = 1, 3 To 10, 12, 14, 19, 20, 22, 28, 35
CheckAee2Eye = "NO"
Exit Function
End Select

Select Case RngAI.Cells(8)
Case Is = 10, 12, 13, 15
CheckAee2Eye = "NO"
Exit Function
End Select

Select Case RngAI.Cells(9)
Case Is = 4, 15, 17, 18, 19, 20
CheckAee2Eye = "NO"
Exit Function
End Select

End Function


You can pick up a little speed by placing the checks most likely to fail at the top of the function, but test the speed first so you don't disturb the mental logic in the function.

RINCONPAUL
10-16-2015, 02:17 PM
Wow, you've blown me away with that workaround SamT LOL

A qualification if I may. Normally this formula resides in a row and looks at cell values within that row ( a Race horse), however on any given day there might be a 1000 rows of racehorses, each with their own set of unique values, that the formula needs to assess and say YES it qualifies of NO it doesn't. How could you adapt the code to handle that situation?

SamT
10-16-2015, 03:11 PM
Drag, or fill down, the Formula down to the bottom of the Column.

If you take a close look at the function, what it is doing to replace your long formula should be apparent.

RINCONPAUL
10-16-2015, 03:22 PM
Ahhh champion stuff SamT. Thankyou again

RINCONPAUL
10-17-2015, 12:34 PM
SamT, just trying to implement your code. In my original post I asked in first 3 lines:

~IF A1 = 7 OR 9 OR 11 OR 12 OR 14
AND B1<>1
AND C1<>4,6,8,~continues on

Your code in reply says (in layman's speak I think):
Select cell A1 and IF A1 = 7, 9, 11, 12, 14 doesn't result in a "NO"
AND IF cell B1 = 1 doesn't result in a "NO"
AND if cell C1 = 4, 6, 8 doesn't result in a "NO" ~ continues on...

Shouldn't the 2nd and 3rd lines of logic be <> rather than = ? Not sure if you can use <> in the code or not?

You'll probably blow me up for asking a dumb question, but that's OK LOL

RINCONPAUL
10-17-2015, 12:47 PM
It's OK SamT, I built a test bed, your code works fine but results in a '0' if conditions are TRUE rather than "YES", but I can work with that. Apologies.

SamT
10-17-2015, 07:15 PM
:banghead::banghead::banghead:

Aww, I left a line out. At the very top of the code the first four lines should be:


Option Explicit

Public Function CheckAee2Eye(RngAI As Range)
CheckAee2Eye = "YES"

Select Case RngAI.Cells(1)


With that line added the logic is
The result is "YES" unless the checks are true (except the very first check: If A1 = then do nothing) The rest are If = then change the result to "NO"

RINCONPAUL
10-17-2015, 08:58 PM
I sorted it out. I had a little trouble adapting it to my real world spreadsheet as the functions reside around col CW..... and I didn't realise that the cell count (?) is from the start of the range you specify and not a count from col A.

I believe you have a mix of different code that means the same thing? IE:


If RngAI.Cells(6) = 4 Then
CheckAee2Eye = "NO"
Exit Function
End If

can be written as:


Select Case RngAI.Cells(6)
Case Is = 4
CheckAee2Eye = "NO"
Exit Function
End Select

To my untrained eye, I couldn't see any difference? You might like to comment either way, as I'll learn something from that. You were using the first part for one and two parameters, and the second part for more than two parameters. Is using the first part really necessary and can it always be written as per the 'Select Case' scenario?

Cheers

SamT
10-18-2015, 01:02 PM
I believe you have a mix of different code that means the same thing?
I am both lazy and a teacher .

Lazy = I use IF for <= 2 conditions. Teaching, I gave you examples on how to use both Functions.

If not in Teach Mode, I would only have used the Select Case Function, written once and copied as many times as needed, then change only the parameters in each.


Select Case RngAI.Cells(1)
Case Is <> 7, 9, 11, 12, 14
CheckAee2Eye = "NO"
Exit Function
End Select
The purpose of the "Exit Function" is speed, no sense checking any other conditions.

Select Case is limited to checking only one thing at a time But when selecting from many options it can't be beat:

Select Case Range("A1").Value
Case Is = 1 to 3
Range("A1").Interior.ColorIndex = 3
Case Is = 4,6
Range("A1").Interior.ColorIndex = 7
Case Is = 5,7,9 to 17
Range("A1").Interior.ColorIndex = 42
Case Else
Range("A1").Interior.ColorIndex = xlColorIndexNone
End Select



the functions reside around col CW
In the VBA Editor, you can use Ctrl+H to Find and Replace. Insure that the F&R parameters are set to only F&R in This Procedure, select an instance of the word "CheckAee2Eye" and replace it with "CheckHorseStats" and repeat for "RngAI" to "RngCW_DE." Always, always, always try to think of very helpful names in your code. I know that I may Ctrl+H a Procedure, Module, or Project several times as I think up a better name.

RINCONPAUL
10-18-2015, 01:35 PM
Well SamT, it proves I've been paying attention in class, hey?....and that a good teacher is always being diverse, by way of presentation.

This exercise has been very productive for me and I thank you again :) The method of inserting a seemingly simple function to refer to a vba code is quite unique, and one I've never seen before, after endless trawling the net for helpful methods. Well done.

SamT
10-18-2015, 02:24 PM
Google for "UDF "User Defined Function"

Paul_Hossler
10-19-2015, 07:46 AM
A slightly different version of SamT's code with a different call. ASSUMPTION is that the data to test against is in the same row as the function




Option Explicit

Public Function CheckAee2Eye() As String

CheckAee2Eye = "NO"

With Application.Caller.Rows(1).EntireRow

Select Case .Cells(1)
Case Is = 7, 9, 11, 12, 14
Case Else
Exit Function
End Select

If .Cells(2) = 1 Then Exit Function

Select Case .Cells(3)
Case Is = 4, 6, 8, 9
Exit Function
End Select

If .Cells(4) = 3 Or .Cells(4) = 8 Then Exit Function

Select Case .Cells(5)
Case Is = 4, 7, 8
Exit Function
End Select

If .Cells(6) = 4 Then Exit Function

Select Case .Cells(7)
Case Is = 1, 3 To 10, 12, 14, 19, 20, 22, 28, 35
Exit Function
End Select

Select Case .Cells(8)
Case Is = 10, 12, 13, 15
Exit Function
End Select

Select Case .Cells(9)
Case Is = 4, 15, 17, 18, 19, 20
Exit Function
End Select

CheckAee2Eye = "YES"

End With

End Function