当单元格有多个值时，是否有一种方法来匹配

Question

我正在读取具有此数据的excel工作表的一列F2:F11的数据

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

我使用这个vba代码来获得匹配到星期六(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

当我这样做时，它会给我5场比赛(是的)，当列中实际上有6个sat时。我哪里做错了?任何帮助都会很感激。由于

Answer 1

当您用逗号分隔字符串M, T, TH, SAT时，您将在结果字符串中获得空间，因此使用TRIM函数:

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

编辑

你在一些字符串中有不间断的空格，所以你需要将Trim和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

Answer 2

问题是在SAT之前的|M, T, TH, SAT|有一个不换行的空格。

在以下代码中修复:

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://learn.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

Answer 3

use:

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

不是

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

你甚至不需要分割单元格内容只需要对它的值

进行检查

For Each y In WaterDays

If InStr(1, y.Value2, "SAT", vbTextCompare) > 0 Then
Debug.Print "Yes"
End If

Next

最后，如果您只对计算"sat"感兴趣，那么您根本不需要任何循环

Set WaterDays = myWKS.Range("F2:F11")
Debug.Print WorksheetFunction.CountIf(WaterDays, "*SAT*")

Answer 4

结果是有一个非换行空间，但是为了处理这个问题，我需要替换Chr(240)而不是Chr(160)。