求助：IF语句未达预期效果，相同单元格值仅半数生效（附VBA代码）

Hey Rahul, let's dig into why your IF statement is acting flaky even when the cell values look identical. This is super common in VBA, and usually boils down to a few sneaky issues that aren't obvious at first glance.

1. 数据类型不匹配（最常见） #

Even if two cells look like they have the same number or text, one might be stored as a text type and the other as a numeric type. For example, cell A2 could be '123 (text-formatted number) while B2 is 123 (raw number)—VBA treats these as completely different values, even though they look identical to you.

• Fix: Convert both values to the same type before comparing. Use Val() for numeric comparisons, or CStr() for text comparisons:

  ' Compare as numbers
  If Val(Sheets("Unique").Cells(i, b).Value) = Val(Sheets(c).Cells(your_target_row, your_target_col).Value) Then
  ' OR compare as text
  If CStr(Sheets("Unique").Cells(i, b).Value) = CStr(Sheets(c).Cells(your_target_row, your_target_col).Value) Then
  

2. 隐藏的空格或不可见字符 #

Cells often have trailing/leading spaces, non-breaking spaces (Chr(160)), or line breaks that you can't see but break the equality check. These tiny characters are easy to miss but make VBA see the values as different.

• Fix: Clean the values before comparing with Trim() and Replace() to eliminate hidden characters:

  Dim uniqueVal As String, sheetVal As String
  ' Remove spaces and non-breaking spaces
  uniqueVal = Trim(Replace(Sheets("Unique").Cells(i, b).Value, Chr(160), " "))
  sheetVal = Trim(Replace(Sheets(c).Cells(your_target_row, your_target_col).Value, Chr(160), " "))
  
  If uniqueVal = sheetVal Then
      ' Your filter logic here
  End If
  

3. 模糊的 Range 引用 #

Your code cuts off at Sheets(c).Ran...—make sure you're referencing the exact cell you want to compare. If you're using AutoFilter, double-check that you're targeting the correct column in the filtered sheet. Ambiguous references can lead to comparing the wrong cells entirely.

• Example of a clear reference:

  ' Assuming you want to compare column A of sheet c with column b of the Unique sheet
  If CStr(Sheets("Unique").Cells(i, b).Value) = CStr(Sheets(c).Cells(i, 1).Value) Then
      ' Apply AutoFilter to column 1 (A) with the cleaned unique value
      Sheets(c).Range("A1:Z" & lr1).AutoFilter Field:=1, Criteria1:=Sheets("Unique").Cells(i, b).Value
  End If
  

4. AutoFilter 缓存/状态问题 #

Sometimes Excel holds onto old filter states that mess with value comparisons. If a sheet was already filtered before your code runs, it might not be comparing the cells you think it is.

• Fix: Clear existing filters at the start of each sheet loop:

  ' Add this inside your c loop to reset filters
  On Error Resume Next ' Ignore error if no filter is applied
  Sheets(c).ShowAllData
  On Error GoTo 0 ' Reset error handling
  

Here's how your code might look with all these fixes applied:

Sub autofilter1()
    Dim b As Integer, c As Integer, i As Integer
    Dim lr As Long, lr1 As Long
    Dim uniqueVal As String, sheetVal As String
    
    For b = 1 To 4
        ' Get last row of unique values in column b of Unique tab
        lr = Sheets("Unique").Cells(Rows.Count, b).End(xlUp).Row
        
        ' Loop through all sheets starting from index 2
        For c = 2 To ActiveWorkbook.Worksheets.Count
            ' Clear any existing filters first
            On Error Resume Next
            Sheets(c).ShowAllData
            On Error GoTo 0
            
            ' Get last row of column A in current sheet
            lr1 = Sheets(c).Cells(Rows.Count, 1).End(xlUp).Row
            
            For i = 2 To lr
                ' Clean values to eliminate hidden characters and standardize type
                uniqueVal = Trim(Replace(Sheets("Unique").Cells(i, b).Value, Chr(160), " "))
                sheetVal = Trim(Replace(Sheets(c).Cells(i, 1).Value, Chr(160), " "))
                
                ' Compare cleaned values
                If uniqueVal = sheetVal Then
                    ' Apply AutoFilter to column 1 (A) with the cleaned unique value
                    Sheets(c).Range("A1:Z" & lr1).AutoFilter Field:=1, Criteria1:=uniqueVal
                    ' Add any additional logic (copy, paste, etc.) here
                End If
            Next i
        Next c
    Next b
End Sub

To see exactly what values VBA is comparing, add a debug line inside your loop:

Debug.Print "Unique Val: '" & uniqueVal & "' | Sheet Val: '" & sheetVal & "'"

Then open the Immediate Window (Ctrl+G in the VBA Editor) to check for hidden spaces, type differences, or mismatched values that you can't see in the sheet.

内容的提问来源于stack exchange，提问作者Rahul Singh