Excess ElseIF statements

[EdwardL95]

Hi All,

Please note I'm still relatively new to VBA and have limited knowledge.

Please see example of data below:

I'm trying to write code to determine what RESULTS would be based on the possible combinations between columns A-E, thus causes my issue of too many ELSEIF statements . Is there a way to do this more efficiently and not have to write out every single combination?

My conditions are:

- MS has be below 35 else it is considered a fail
- If any columns between A-E are between 0 to 30 then it would be considered positive for that specific column and thus transcribed into Results


Below is an example of my code just for these possible outcomes in the dataset above.

For J = 2 To 8

    P = Cells(J, "A").Value
    M = Cells(J, "B").Value
    K = Cells(J, "C").Value
    R = Cells(J, "D").Value
    B = Cells(J, "E").Value
    Ms = Cells(J, "F").Value
    
    If _
        Ms > 38 Then
        Cells(J, "G").Value = "Fail"
        
    ElseIf _
        P < 35 And _
        M < 35 And _
        K < 35 And _
        R < 35 And _
        B < 35 Then _
        Cells(J, "G").Value = "P M K R B"
        
    ElseIf _
        M < 35 And _
        K < 35 And _
        R < 35 And _
        B < 35 Then _
        Cells(J, "G").Value = "M K R B"
        
    ElseIf _
        K < 35 And _
        R < 35 And _
        B < 35 Then _
        Cells(J, "G").Value = "K R B"
        
    ElseIf _
        R < 35 And _
        B < 35 Then _
        Cells(J, "G").Value = "R B"
        
    ElseIf _
        B < 35 Then _
        Cells(J, "G").Value = "B"

End If

Next J

Thanks,
Ed

 

[ranman256]

don't use ELSEIF
use SELECT CASE statement. Easier to read:

select case true
case        Ms > 38
        Cells(J, "G").Value = "Fail"
       
case    P < 35 And _
        M < 35 And _
        K < 35 And _
        R < 35 And _
        B < 35

        Cells(J, "G").Value = "P M K R B"
end select

 

[johnnyL]

This was my version I came up with similar to @ranman256's suggestion:

    For J = 2 To 8
         P = Cells(J, "A").Value
         M = Cells(J, "B").Value
         K = Cells(J, "C").Value
         R = Cells(J, "D").Value
         B = Cells(J, "E").Value
        Ms = Cells(J, "F").Value
'
        Select Case True
            Case Ms > 38: Cells(J, "G").Value = "Fail"
'
            Case P < 35 And M < 35 And K < 35 And R < 35 And B < 35: Cells(J, "G").Value = "P M K R B"
'
            Case M < 35 And K < 35 And R < 35 And B < 35: Cells(J, "G").Value = "M K R B"
'
            Case K < 35 And R < 35 And B < 35: Cells(J, "G").Value = "K R B"
'
            Case R < 35 And B < 35: Cells(J, "G").Value = "R B"
'
            Case B < 35: Cells(J, "G").Value = "B"
'
            Case Else: Cells(J, "G").Value = "Negative"
        End Select
    Next J

Edit: Forgot the Negative test

 

[EdwardL95]

@ranman256 and @johnnyL thanks for the suggestions however my problem isn't so much writing 7 elseif or 7 cases. It's the issue of there being approx. 120 combinations with P, M,K, R & B.

etc. M K R B, M K R, MK ,M , M R, M R B, MKB.... and the list goes on with more combinations.

I do not want to brute force write every single combination be it elseif or select case and was wondering if there was a way around this.

The dataset I showed was only an example of what my data looks like but doesn't show all the possible outcomes.

Apologies if I wasn't descriptive enough.

Thanks, Ed

 

[Eric W]

How about:

Sub test1()
Dim hdraddr As String, rowaddr As String, str1 As String, r As Long

    hdraddr = Range("A1:E1").Address
    For r = 2 To 8
        rowaddr = Cells(r, "A").Resize(, 5).Address
        str1 = Evaluate("TEXTJOIN("" "",1,IF((" & rowaddr & ">0)*(" & rowaddr & "<=30)," & hdraddr & ",""""))")
        If Len(str1) = 0 Then str1 = "Negative"
        If Cells(r, "F") > 30 Then str1 = "FAIL"
        Cells(r, "G") = str1
    Next r
    
End Sub

 

[johnnyL]

Book1
	A	B	C	D	E	F	G
1	P	M	K	R	B	MS	Results
2	34	34	34	34	34	38	P M K R B
3	-	34	34	34	34	38	M K R B
4	-	-	34	34	34	38	K R B
5	-	-	-	34	34	38	R B
6	-	-	-	-	34	38	B
7	-	-	-	-	-	40	Fail
8	-	-	-	-	-	38	Negative
Sheet1

Hey there @Eric W, I can't test the TEXTJOIN because I don't have the proper excel to do so. But I think you need to correct a few values in your code.

<= 30 should be <= 34 Or just < 35

If Cells(r, "F") > 30 should be If Cells(r, "F") > 38

 

[Eric W]

<= 30 should be <= 34 Or just < 35

If Cells(r, "F") > 30 should be If Cells(r, "F") > 38

My conditions are:

- MS has be below 35 else it is considered a fail
- If any columns between A-E are between 0 to 30 then it would be considered positive for that specific column and thus transcribed into Results

Yes, it appears that I may have some wrong values, but it also seems there's some ambiguity in the requirements. I'll wait for the OP to chime in.

Also, you might not have TEXTJOIN, but I could replace that line with a short loop, which might even be better. I'll wait to see on that too.

 

[johnnyL]

Ahh. I see the ambiguity now. The original code is not consistent with the original text. My apologies, if required.

 

[EdwardL95]

@Eric W and @johnnyL Thanks for this, didn't think to do it like that.

Apologies for the ambiguity haha, I can change the values myself.

Thanks!

 

[Eric W]

Glad we could help!