Is there a way to match when the cell has several values-CodePudding

I am reading the data from one column F2:F11 of an excel worksheet that has this data

|SAT|
|SAT|
|SAT|
|SAT|
|M, T, TH, SAT|
|SUN, W|
|SUN, W, F|
|SAT|
|T, F, SUN|
|W|

I'm using this vba code to get the matches to Saturday (SAT)

Sub Test()

Dim WaterDays As Range
Set myWKS = Worksheets("Water")
Set WaterDays = myWKS.Range("F2:F11")

For Each y In WaterDays
   vParts = Split(y, ",")
   For Each dy In vParts
      If StrComp(dy, "SAT") = 0 Then
        Debug.Print ("Yes")
      End If
   Next dy
 Next y
End Sub

When I do this it will give me 5 matches (Yes), when there are actually 6 SATs in the column. What am I doing wrong here? Any help would be appreciated. Thanks

CodePudding user response：

When you split string M, T, TH, SAT by comma you get space in resulting strings, so use TRIM function:

...StrComp(Trim(dy), "SAT")...

Edit

You have non breaking spaces in some strings so you need combine Trim and Substitute:

Sub Test()

Dim WaterDays As Range, cel As Range
Set myWKS = Worksheets("Water")
Set WaterDays = myWKS.Range("F2:F11")

For Each y In WaterDays
   vParts = Split(y, ",")
   For Each dy In vParts
      tdy = Application.Substitute(Trim(dy), Chr(160), "")
      If StrComp(tdy, "SAT") = 0 Then
        Debug.Print ("Yes")
      End If
   Next dy
 Next y
End Sub

CodePudding user response：

The problem is a non-breaking space in |M, T, TH, SAT| before SAT.

This is fixed in the following code:

Option Explicit

Public Sub test()

Debug.Print "--- check for SAT"
checkForWeekday "SAT"

Debug.Print "--- check for TU"
checkForWeekday "TU"
End Sub


Public Sub checkForWeekday(strDay As String)

Dim WaterDays As Range
Set myWKS = Worksheets("Water")
Set WaterDays = myWKS.Range("F2:F11")


Dim arrValues As Variant
arrValues = WaterDays.Value2

Dim i As Long, values As String

For i = 1 To UBound(arrValues, 1)
    values = Application.WorksheetFunction.Clean(arrValues(i, 1)) 'clean other non-printable signs https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.clean
    values = Replace(values, Chr(160), " ") 'non-breaking space
    
    values = "|" & Replace(values, ", ", "|") & "|"   'add comma at the beginning and the end to have clear separators
    If InStr(values, "|" & strDay & "|") Then    'then you can check for |*|
        Debug.Print values, "yes"
    Else
        Debug.Print values, "no"
    End If
Next

End Sub

CodePudding user response：

use:

If InStr(1, dy, "SAT", vbTextCompare) > 0 Then

instead of

If StrComp(dy, "SAT") = 0 Then

You don't even have to split the cell content and just run that check on its value

            For Each y In WaterDays
            
                If InStr(1, y.Value2, "SAT", vbTextCompare) > 0 Then
                    Debug.Print "Yes"
                End If
                        
            Next

Finally, should you be interested in counting the "SAT"s only , than you don't need any loop at all

        Set WaterDays = myWKS.Range("F2:F11")

            Debug.Print WorksheetFunction.CountIf(WaterDays, "*SAT*")