[VBA] Case like "X", "Y", "Z", "" not working

[RockandGrohl]

Hi all,

I'm declaring my source as "src" and my discount as "dis"

src will always be a value, but dis can either be a value or blank.


An example will be src = NQS and dis = "", or src = NQS and dis = NQS20

My code is below:

src = Cells(ActiveCell.Row, "F").Valuedis = Cells(ActiveCell.Row, "G").Value
Select Case src
    
    'Attribution Model 1
    Case src Like "ARC", "BAC", "ICP", "IPRT", "JGRT", "KMG", "NAD", "NQS", "OMRT", "OSG*", "RCH", "ROPJG", "RTSUP", "SUP", "TIN*", "TLA*", "TRN", "WPR*"
        Select Case dis
            Case dis Like "", "ARC*", "BAC*", "ICP*", "IPRT*", "JGRT*", "KMG*", "NAD*", "NQS*", "OMRT*", "OSG*", "RCH*", "ROPJG*", "RTSUP*", "SUP*", "TIN*", "TLA*", "TRN*", "WPR*"
                Cells(ActiveCell.Row, "AE").Value = "Y"
            Case Else
    'Attribution Model 2
                Cells(ActiveCell.Row, "AE").Value = "N"
            End Select
End Select

In that first example, what happens is it detects the src matches the initial set of cases as NQS is in that list. Then it goes to select case dis and shows it can be either blank or one of the same codes with a wildcard after.

What happens is even though src = NQS and dis = "", it goes to "Case Else" and puts a "N" in column AE.

This needs to be a Y because the logic is src = list and disc = list or blank.

Thanks!

 

[RockandGrohl]

I don't think you would need a million nested Ifs for that.

How do you check if the discount/source code is valid?

Does the discount code always start with the same letters as the source code?

How to you check for the URL?

I have a field for URL (1 or 0)

There's a more comprehensive explanation on Stack Overflow, admittedly this thread was specifically about one tiny part of the whole issue.

https://stackoverflow.com/questions/58487850/can-i-include-a-blank-in-case-like-x-y-z

I had the logic all set up but it seemed to fall over when a blank was included. I think the function provided was to pre-emptively assign a blank value as a Boolean, but I'm not really sure.

 

[Norie]

I don't have time right now to look at the other thread but based on what you've posted here this is what I came up with.

Obviously it's untested and it's longer than the solution you posted but, to me anyway, it makes things clearer.

Sub Test()
Dim src As String, dis As String


    src = Cells(ActiveCell.Row, "F").Value
    dis = Cells(ActiveCell.Row, "G").Value
     
    Cells(ActiveCell.Row, "AE") = ClientBooking(src, dis)

End Sub

Function ClientBooking(src As String, dis As String) As String
Dim arrSource As Variant
Dim arrWeb As Variant
Dim Res As Variant
Dim I As Long

    arrSource = Array("ARC", "BAC", "ICP", "IPRT", "JGRT", "KMG", "NAD", "NQS", "OMRT", "RCH", "ROPJG", "RTSUP", "SUP", "TRN")

    arrWeb = Array("OSG", "TIN", "TLA", "WPR")

    ' default, assumes not client booking
    ClientBooking = "F"

    ' check for valid source code
    Res = Application.Match(src, arrSource, 0)

    If Not IsError(Res) Then
        ' check discount code
        If dis Like src & "*" Or dis = "" Then
            ClientBooking = "Y"
            Exit Function
        End If
    End If

    ' check for web source code
    Res = Application.Match(Left(src, 3), arrWeb, 0)

    If Not IsError(Res) Then
        ' check for URL
        ' check for discount code
    End If
 
   ' check if discount code is valid
   For I = LBound(arrSource) To UBound(arrSource)
       If Left(dis, Len(arrSource(I))) = arrSource(I) Then
           ClientBooking = "Y"
           Exit Function
       End If
   Next I

End Function

 

[RockandGrohl]

I think that does make things clearer because it's laid out, shall we say, chonologically?

 

[RoryA]

Go for it, as far as I can tell you've not come in here to help but to taddle about a thread that received no help. I posted my answer I received from Stack Overflow here to help any future posters looking for help on the same kind of multi-critieria Case statement.

You're still supposed to provide links if you cross-post, please. There are no exceptions.

 

[jasonb75]

FYI I did come here with the intention of helping, but when I see a casual mention that you received an answer elsewhere I then think that I could be wasting my time repeating the same answers.

Also, as you only mentioned the other thread because you received an answer there, it could mean that you have unanswered / ongoing discussions for the same topic in other forums that you haven't mentioned. To be clear, I'm not accusing you of doing this, I'm just saying that it does happen.

Anyway back to what I was going to point out so that you can see why your original method wasn't working.

Either of the following methods work but neither is suitable for what you're doing. The first example only accepts exact matches, not wildcards. The second only accepts one possible match per line, meaning that you would need a 'Case src Like' line for each string to be checked. A quick glance at Norie's suggestion, it looks as though this is the method used, but by cycling through the strings in an array rather than repeating several lines of code over and over.

Select Case scr
    Case "ARC" Or "BAC", "ICP", "IPRT", "JGRT", "KMG", "NAD"

Select Case True
    Case src Like "OSG*"

As you had tried a mixture of the 2 methods, it appears that your code was compiling without error but was not producing a useful result.