NOTE: This question works off of my previous post here.
Here is a background of my problem. I have multiple test files of the same format in a folder. I need to count the specific amount of "YES" in a column in each work book. From my previous question, I was able to obtain a macro that traverses through each file, count the "YES", and put the counter in a Master Workbook. The following macro shows this code:
Private Sub CommandButton1_Click()
Dim r As Range
With Worksheets("Sheet1")
For Each r In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
r.Offset(0, 1).Value = getYesCount(r.Value)
Next
End With
End Sub
Function getYesCount(WorkBookName As String) As Long
Const FolderPath As String = "C:\Users\khanr1\Desktop\CodeUpdateTest\"
If Len(Dir(FolderPath & WorkBookName)) Then
With Workbooks.Open(FolderPath & WorkBookName)
With .Worksheets("Sheet2")
getYesCount = Application.CountIf(.Range("D:D"), "YES")
End With
.Close False
End With
Else
Debug.Print FolderPath & WorkBookName; ": Not Found"
End If
End Function
Now the issue I have is to add a feature that counts the "YES" ONLY if there is a String "Warning" adjacent to it. Please refer to the following snapshot of one of the test files:
Here is a snapshot of the Master Workbook (CountResults.xlsm):
My idea was to create an 'If' function that checks to see if the cell contains "Warning" and if so, proceed to count the YES column. And as a side note, I cannot make a macro that just sees if the column has a string, because the column may have multiple strings. It needs to specify the "Warning" or "Warning*" (in case there are details following it).
