Cannot find key terms

[trevormay99]

Hello, I am still very new at VBA and was wondering if anyone had an reccommendations to fix this reoccurring issue I am having. I am using the code below to find a data entry in column m as well as the most frequently occuring data entries in column n for the key term found in column M. I used this code previously with no issues however when I changed the term to be searched to, "FPL #1 Entry Pull Rolls" it is unable to find the data entry in column m despite 57 iterations existing exactly as described with no leading or trailing spaces. The code was able to find some data entries in column m but not all, I have already changed column m to text strings and am stuck, any help is appreciated.

[/B]
Sub FindFPLDataInSheet5()
    Dim wsSource As Worksheet
    Dim lastRow As Long, i As Long
    Dim fplCount As Long
    Dim fplFound As Boolean
    Dim topValues() As Variant
    Dim frequencyArr() As Variant
    
    ' Define your source worksheet
    Set wsSource = ThisWorkbook.Sheets("Sheet11") ' Change to your source sheet name
    
    ' Find the last row in column M
    lastRow = wsSource.Cells(wsSource.rows.Count, "M").End(xlUp).Row
    
    ' Arrays to store frequencies and top values
    ReDim frequencyArr(1 To lastRow, 1 To 2)
    ReDim topValues(1 To 10, 1 To 2)
    
    ' Loop through column M to count occurrences of "FPL #1 Entry Pull Rolls"
    For i = 1 To lastRow
        If InStr(1, wsSource.Cells(i, "M").Text, "FPL #1 Entry Pull Rolls", vbTextCompare) > 0 Then
            fplCount = fplCount + 1
            fplFound = True
            frequencyArr(fplCount, 1) = wsSource.Cells(i, "N").value
            frequencyArr(fplCount, 2) = Application.WorksheetFunction.CountIf(wsSource.Columns("N"), frequencyArr(fplCount, 1))
        End If
    Next i
    
    ' Sort frequency array by frequency count
    frequencyArr = SortArrayDescending(frequencyArr)
    
    ' Populate top values array
    If fplFound Then
        For i = 1 To 10
            topValues(i, 1) = frequencyArr(i, 1)
            topValues(i, 2) = frequencyArr(i, 2)
        Next i
    End If
    
    ' Display top values in Sheet5
    If fplFound Then
        Dim wsOutput As Worksheet
        Set wsOutput = ThisWorkbook.Sheets("Sheet5") ' Change to your output sheet name
        
        ' Clear previous data in Sheet5
        wsOutput.Cells.Clear
        
        ' Output top values to Sheet5
        With wsOutput
            .Range("A1").value = "Top 10 Values"
            .Range("A2").Resize(10, 2).value = topValues
        End With
    Else
        MsgBox "'FPL #1 Entry Pull Rolls' not found in column M.", vbExclamation
    End If
End Sub

Function SortArrayDescending(inputArray As Variant) As Variant
    Dim tempArray() As Variant
    Dim i As Long, j As Long
    Dim temp1 As Variant, temp2 As Variant
    
    tempArray = inputArray
    For i = LBound(tempArray) To UBound(tempArray) - 1
        For j = i + 1 To UBound(tempArray)
            If tempArray(i, 2) < tempArray(j, 2) Then
                temp1 = tempArray(i, 1)
                temp2 = tempArray(i, 2)
                tempArray(i, 1) = tempArray(j, 1)
                tempArray(i, 2) = tempArray(j, 2)
                tempArray(j, 1) = temp1
                tempArray(j, 2) = temp2
            End If
        Next j
    Next i
    SortArrayDescending = tempArray
End Function

[B]

 

[DanteAmor]

I did a test by copying and pasting the text: "FPL #1 Entry Pull Rolls" into some cells in column M and your macro works.

Try copying and pasting the text into your cells and let me know.