时间戳

首页 > 解决方案 > 运行时错误“13”：使用 if 语句时类型不匹配

vba - 运行时错误“13”：使用 if 语句时类型不匹配

问题描述

无论如何要使下面的 [] like XXX 或 [] like YYY 部分更干净？

谢谢谢谢@Tim Williams，但我该如何改变它的形式：

Sub Only_Choose_Unders()
  ' Only_Choose_Unders Macro
  If ['Lab UP no 360 Chem OPP'!D2] = "1A. Madrid" Or ['Lab UP no 360 Chem OPP'!D2] = "1B. Madrid" Or ['Lab UP no 360 Chem OPP'!D2] = "2A. Barcelona" Or ['Lab UP no 360 Chem OPP'!D2] = "2B. Barcelona" Or ['Lab UP no 360 Chem OPP'!D2] = "3. Valencia" Or ['Lab UP no 360 Chem OPP'!D2] = "4A. Malaga" Or ['Lab UP no 360 Chem OPP'!D2] = "4B. Sevilla" Or ['Lab UP no 360 Chem OPP'!D2] = "5. Bilbao" Or ['Lab UP no 360 Chem OPP'!D2] = "6. Canarias" Or ['Lab UP no 360 Chem OPP'!D2] = "7. Baleares" Or ['Lab UP no 360 Chem OPP'!D2] = "8. NorOeste" Or ['Lab UP no 360 Chem OPP'!D2] = "(Multiple Items)" Then
    With Sheets("Lab UP no 360 Chem OPP").Range("m24:m5000")
        .AutoFilter Field:=8, Criteria1:="<0"
        .AutoFilter Field:=9, Criteria1:="<0"
        .AutoFilter Field:=10, Criteria1:="10", _
         Operator:=xlBottom10Percent
         .AutoFilter Field:=11, Criteria1:="10", _
         Operator:=xlBottom10Percent
    End With
Else
    With Sheets("Lab UP no 360 Chem OPP").Range("m24:m5000")
        .AutoFilter Field:=8, Criteria1:="<0"
        .AutoFilter Field:=9, Criteria1:="<0"
        .AutoFilter Field:=10, Criteria1:="50", _
        Operator:=xlBottom10Percent
        .AutoFilter Field:=11, Criteria1:="50", _
        Operator:=xlBottom10Percent
    End With
 End If
End Sub

标签： vbaif-statementmismatch

解决方案

我会避免使用方括号表示法，并且您需要在获得正确结果之前修改您的检查：

If ['Lab UP no 360 Chem OPP'!D2] Like "1A. Madrid" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "1B. Madrid" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "2A. Barcelona" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "2B. Barcelona" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "3. Valencia" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "4A. Malaga" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "4B. Sevilla" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "5. Bilbao" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "6. Canarias" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "7. Baleares" Or _
   ['Lab UP no 360 Chem OPP'!D2] Like "8. NorOeste" Then

Select Case虽然为您提供了更简单的语法：

Sub Only_Choose_Unders()
    Dim ws As Worksheet, crit
    
    Set ws = ThisWorkbook.Worksheets("Lab UP no 360 Chem OPP")
    Select Case ws.Range("D2").Value
        Case "1A. Madrid", "1B. Madrid", "2A. Barcelona", "2B. Barcelona", _
             "3. Valencia", "4A. Malaga", "4B. Sevilla", "5. Bilbao", _
             "6. Canarias", "7. Baleares", "8. NorOeste"
              crit = "50"
        Case Else: crit = "10"
    End Select
    
    With ws.Range("M24:M5000")
        .AutoFilter Field:=8, Criteria1:="<0"
        .AutoFilter Field:=9, Criteria1:="<0"
        .AutoFilter Field:=10, Criteria1:=crit, Operator:=xlBottom10Items
        .AutoFilter Field:=11, Criteria1:=crit, Operator:=xlBottom10Items
    End With
End Sub

推荐阅读