Excel VBA nested case function for multiple conditions

Question

I have a sql query and want to replicate in Excel VBA. I am having trouble using multiple case statements

example columns

column(a) - segment_nbr

column(b) - ltv 

segment_nbr ltv 

1   2.1526521   
4   3.01348283  
1   1.49385324  
1   1.84731871  
1   1.29541322  
1   0.55659018  
2   2.33690417  
1   1.34068404  
2   1.54078719  
1   0.74087837  
3   1.93278303  
1   1.38347042  
4   1.64194326

I want to build a function that would replicate the following example nested if /case formula:

=if(and($A1=1,$B1<=0.9),100.01,IF(and($A1=1,$B1<=2.0),201.01,IF(and($A1=1,$B1<=3.0),-23.26,IF(and($A1=2,$B1<=0.9),-99.98,IF(and($A1=3,$B1<=1.3),199.98, IF(and($A1=4,$B1<=0.44),-32.43,IF(and($A1=4,$B1<=1.6),160.9,"" )))

I tried the following but not working: it is not taking the segment_nb argument.

any thoughts on how I can correct it?

Function ltv_w(segment_nbr, ltv )

Select Case ltv 
    Case Is <= 0.9 And segment_nbr = 1
        ltv_w = 100.01
    Case Is <= 2.0 And segment_nbr = 1
        ltv_w = 201.01
    Case Is <= 3.0 And segment_nbr = 1
        ltv_w = -23.26
    Case Is <= 0.9 And segment_nbr = 2
        ltv_w = -99.98
    Case Is <= 1.3 And segment_nbr = 3
        ltv_w = 199.98
    Case Is <= 0.44 And segment_nbr = 4
        ltv_w = -32.43
    Case Is <= 1.6 And segment_nbr = 4
        ltv_w = 160.9

End Select

End Function

Your code worked for me. How do you call your function in the worksheet? (formula) — rellampec
– rellampec, Commented Mar 22, 2018 at 20:36
yes i did. when i tried my original code, it was not taking the segment change — Sparta
– Sparta, Commented Mar 23, 2018 at 2:21

user6698332 · Accepted Answer · 2018-03-22 06:05:11Z

2

First of all - change the sequence of checks.
Check segment_nbr at 1-st Case level, then at the 2-nd level check ltv.

Function ltv_w(segment_nbr, ltv)

    Select Case segment_nbr
        Case 1
            Select Case ltv
                Case Is <= 0.9: ltv_w = 100.01
                Case Is <= 2#:  ltv_w = 201.01
                Case Is <= 3#:  ltv_w = -23.26
            End Select

        Case 2
        Case 3
        Case 4
    End Select
End Function

Be carefull with Case Is <= sequence.

And once more... Never compare Doubles for equality.
So conditions like <= 3# need to be converted to Not > 3#.
.

edited Mar 22, 2018 at 6:05

answered Mar 22, 2018 at 5:52

user6698332

4273 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2018-03-22 05:48:42Z

1

Using Select Case ltv, checks only the value of ltv, it's not like using If and adding an And, it ignores the second criteria.

You could "cheat" the Select Case a little, you can use Select Case True, and then nested below modify your code a little:

Case ltv <= 0.9 And segment_nbr = 1

Try the code below:

Function ltv_w(segment_nbr, ltv)

Select Case True
    Case ltv <= 0.9 And segment_nbr = 1
        ltv_w = 100.01
    Case ltv <= 2# And segment_nbr = 1
        ltv_w = 201.01
    Case ltv <= 3# And segment_nbr = 1
        ltv_w = -23.26
    Case ltv <= 0.9 And segment_nbr = 2
        ltv_w = -99.98
    Case ltv <= 1.3 And segment_nbr = 3
        ltv_w = 199.98
    Case ltv <= 0.44 And segment_nbr = 4
        ltv_w = -32.43
    Case ltv <= 1.6 And segment_nbr = 4
        ltv_w = 160.9
End Select

End Function

answered Mar 22, 2018 at 5:48

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

1 Comment

Shai Rado Over a year ago

@Sparta you're welcome, check the following link how we say thanks on SO stackoverflow.com/help/someone-answers

Dinesh Vilas Pawar · Accepted Answer · 2018-03-22 06:20:12Z

As per my understanding, you can achieve this stuff with if else statement also, check below code It modified as per your excel formula.

''''
Function ltv_w(segment_nbr, ltv)

Select Case segment_nbr
    Case Is = 1

    Select Case ltv
    Case Is <= 0.9
    ltv_w = -99.98

    Case Is <= 2#
    ltv_w = 201.01

    Case Is <= 3#
    ltv_w = -23.26

    Case Else
    ltv_w = ""

    End Select

    Case Is = 2

    Select Case ltv
    Case Is <= 0.9
    ltv_w = -99.98

    Case Else
    ltv_w = ""

    End Select

    Case Is = 3

    Select Case ltv
    Case Is <= 1.3
    ltv_w = 199.98

    Case Else
    ltv_w = ""

    End Select

    Case Is = 4

    Select Case ltv
    Case Is <= 0.44
    ltv_w = -32.43

    Case Is <= 1.6
    ltv_w = 160.9

    Case Else
    ltv_w = ""

    End Select

Case Else
ltv_w = ""

End Select
End Function

''

Collectives™ on Stack Overflow

Excel VBA nested case function for multiple conditions

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related