如何将细胞的内容与特定字符串进行比较

Question

初始化许可证

dim transientLicense AS integer
transientLicense=0
dim steadyLicense AS integer
steadyLicense=0
dim staticLicense AS integer
staticLicense=0

检查条件

if( (value.cell(AH) =("radial vibration" or "acceleration" or "acceleration2" or "velocity" or "velocity2")) && (value.cell(W)="yes") && (value.cell(D)="active") Then
 transientLicense++
else if( (value.cell(AH) =("radial vibration" or "acceleration" or "acceleration2" or "velocity" or "velocity2")) && (value.cell(W)="no") && (value.cell(D)="active") Then
 steadyLicense++
else if((value.cell(AH)=("axial vibration" or "temperature" or "pressure") && (value.cell(D)="active")) Then
 staticLicense++

我如何在适当的VBA语法

Answer 1

edit 添加 WorkSheetFunction.CountIF()选项

您可以走两种方法：

• AutoFilter()选项

  Option Explicit
  Sub main()
      Dim transientLicense As Integer
      Dim steadyLicense As Integer
      Dim staticLicense As Integer
      Dim arr1 As Variant, arr2 As Variant
      arr1 = Array("radial vibration", "acceleration", "acceleration2", "velocity", "velocity2") '<--| set your first values list
      arr2 = Array("axial vibration", "temperature", "pressure") '<--| set your 2nd values list
      With Worksheets("Licenses") '<-| reference your relevant worksheet (change "Licenses" to your actual worksheet name)
          With .Range("D1", .Cells(.Rows.Count, "AH").End(xlUp)) '<--| reference its columns D to AH range from row 1 down to column AH last not empty row
              .AutoFilter Field:=1, Criteria1:="active" '<--| filter referenced cells on 1st column ("D") with "active"
              .AutoFilter Field:=31, Criteria1:=arr1, Operator:=xlFilterValues '<--| filter referenced cells on 31th column ("AH") with arr1 list
              .AutoFilter Field:=20, Criteria1:="yes" '<--| filter referenced cells on 20th  column ("W") with "yes"
              transientLicense = .Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
              .AutoFilter Field:=20, Criteria1:="no" '<--| filter referenced cells on 20th  column ("W") with "no"
              steadyLicense = .Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
              .AutoFilter Field:=20 '<--|remove filter on 20th columncolumn
              .AutoFilter Field:=31, Criteria1:=arr2, Operator:=xlFilterValues '<--| filter referenced cells on 31th  column ("AH") with arr2 list
             staticLicense = .Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
          End With
          .AutoFilterMode = False
      End With
  End Sub
  

  唯一的要求是第1行必须有标题

---

• WorkSheetFunction.CountIF()选项

  Option Explicit
  Sub main()
      Dim transientLicense As Integer
      Dim steadyLicense As Integer
      Dim staticLicense As Integer
      Dim arr1 As Variant, arr2 As Variant, elem As Variant
      arr1 = Array("radial vibration", "acceleration", "acceleration2", "velocity", "velocity2") '<--| set your first values list
      arr2 = Array("axial vibration", "temperature", "pressure") '<--| set your 2nd values list
      With Worksheets("Licenses") '<-| reference your relevant worksheet (change "Licenses" to your actual worksheet name)
          With .Range("D1", Cells(Rows.Count, "AH").End(xlUp)) '<--| reference its columns D to AH range from row 1 down to column AH last not empty row
              For Each elem In arr1 '<--| loop through 1st array list
                  transientLicense = transientLicense + WorksheetFunction.CountIfs(.Columns(1), "active", .Columns(20), "yes", .Columns(31), elem) '<-- update 'transientLicense' for every record matching: "active" in referenced range column 1(i.e. "D"), "yes" in referenced range column 20 (i.e. "W") and current list element in referenced range column 31 (i.e. "AH")
                  steadyLicense = steadyLicense + WorksheetFunction.CountIfs(.Columns(1), "active", .Columns(20), "no", .Columns(31), elem) '<-- update 'steadyLicense' for every record matching: "active" in referenced range column 1(i.e. "D"), "no" in referenced range column 20 (i.e. "W") and current list element in referenced range column 31 (i.e. "AH")
              Next elem
              For Each elem In arr2 '<--| loop through 2nd array list
                  staticLicense = staticLicense + WorksheetFunction.CountIfs(.Columns(1), "active", .Columns(31), elem) '<-- update 'staticLicense' for every record matching: "active" in referenced range column 1(i.e. "D") and current list element in referenced range column 31 (i.e. "AH")
              Next elem
          End With
      End With
  End Sub
  

Answer 2

您应该尝试这样的方式

Sub Demo()
If ((Range("AH1").Value = "radial vibration") Or (Range("AH1").Value = "acceleration") Or (Range("AH1").Value = "acceleration2") Or (Range("AH1").Value = "velocity") Or (Range("AH1").Value = "velocity2") Or (Range("AH1").Value = "velocity2")) And ((Range("W1").Value = "yes")) And ((Range("D1").Value = "active")) Then
    transientLicense = transientLicense + 1
End If

End Sub