本文介绍了如何将单元格的内容与特定字符串进行比较的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
将许可证初始化为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
这篇关于如何将单元格的内容与特定字符串进行比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!