如何将单元格的内容与特定字符串进行比较 [英] How to compare content of a cell against specific strings

问题描述

将许可证初始化为0

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语法编写这个

how do I write this in proper vba syntax

推荐答案

编辑添加 WorkSheetFunction.CountIF（）选项

你可以采取两种方式：

• AutoFilter（）期权

• AutoFilter() option

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行必须有标题

the only requirement being row 1 must have headers

• WorkSheetFunction.CountIF（）选项

• WorkSheetFunction.CountIF() option

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

这篇关于如何将单元格的内容与特定字符串进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！