How to compare content of a cell against specific

Question

Initialising no of licenses to 0

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

Checking conditions

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++

how do I write this in proper vba syntax

Answer 1

You should try such way

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

Answer 2

edit to add a WorkSheetFunction.CountIF() option

you could go two ways:

• 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
  

  the only requirement being row 1 must have headers

---

• 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