VBA中CountIf统计超255字符查找结果计数为0的问题排查

Hey there! Let's sort out that problem where your code returns 0 for comments longer than 255 characters. The root cause here is Excel's built-in limitation: the CountIf function can't handle search strings longer than 255 characters. Instead of relying on CountIf, we can loop through the target range directly and compare full strings—this works no matter how long your comments are.

Here's the corrected code: #

Sub CommentCount()
    Dim CSAT_Comments As Workbook
    Dim comment As Worksheet
    Dim match As Worksheet
    Dim CommentString As String
    Dim MatchRow As Integer
    Dim CommentRange As Range
    Dim CRange As Range
    Dim DuplicateCount As Integer
    Dim cell As Range ' New variable to iterate through the search range
    
    ' Set up workbook and worksheet references
    Set CSAT_Comments = ActiveWorkbook
    Set comment = CSAT_Comments.Worksheets("Qualitative Analysis_2018 Cycle")
    Set match = CSAT_Comments.Worksheets("Consolidated Comments")
    
    ' Define the range of comments to check (without using select/activate)
    Set CommentRange = match.Range("A2", match.Range("A2").End(xlDown)).SpecialCells(xlCellTypeVisible)
    
    ' Turn off screen updating to speed up the macro
    Application.ScreenUpdating = False
    
    For Each CRange In CommentRange
        ' Use the FULL comment string (no more truncating to 255 chars!)
        CommentString = CRange.Value
        MatchRow = CRange.Row
        DuplicateCount = 0 ' Reset count for each new comment
        
        ' Loop through every cell in the search range (AK:BL)
        For Each cell In comment.Range("AK:BL").SpecialCells(xlCellTypeVisible)
            ' Compare full strings (case-insensitive by default; add vbBinaryCompare inside the If for case-sensitive checks)
            If cell.Value = CommentString Then
                DuplicateCount = DuplicateCount + 1
            End If
        Next cell
        
        ' Write the final count to column B next to the comment
        match.Range("B" & MatchRow).Value = DuplicateCount
    Next CRange
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

Key changes made: #

• Removed string truncation: We no longer use Left(CRange.Value, 255)—now we work with the full comment text.
• Replaced CountIf with a loop: Directly iterating through each cell in the target range lets us compare full-length strings, bypassing the 255-character limit of CountIf.
• Improved efficiency: Moved Application.ScreenUpdating = False outside the loop (instead of toggling it inside) and removed unnecessary .Activate/.Select calls—these make the macro run faster and avoid interface glitches.
• Reset count for each comment: Ensures each comment's count starts at 0, preventing carry-over from previous iterations.

内容的提问来源于stack exchange，提问作者Karthik P B