Good morning/afternoon all
I'm trying to figure out how to use CountIfs for a monthly Management report strictly using VBA. I can do it using arrays in excel formulas, but the language barrier from Formula to VBA is just beyond me at the present.
Here's what I got so far.
For context, the macro above first finds the last entry made on Column A, From there, it determines the contents of what's in Column B 2 rows below it. Then it uses that as the criteria for my CountIf statement.
This works when I have multiple criteria with different Range. But now I need to count the same range AC1 through end row AC. The contents have 17 different "statuses". I only want to count if the status is "U,N,R,L,P,O" not the rest. I'm stumped...
any help in the right direction would be appreciated.
(Edit: I should probably say that I know I can just add the countifs together using countif statement and "U" + countif statement and "N" + countif statement and "R" Etc. But that's extremely clunky. I'd rather find an easier, less wordy version of doing this)
I'm trying to figure out how to use CountIfs for a monthly Management report strictly using VBA. I can do it using arrays in excel formulas, but the language barrier from Formula to VBA is just beyond me at the present.
Here's what I got so far.
Code:
Dim ws1 As Worksheet
Dim rg As Worksheet
Dim LRColA As Long
Dim LastRow As Long
Set ws1 = Sheets("Management Report")
Set rg = Sheets("RG-ALLDATA")
LRColA = Range("A:A").Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
ws1.Range("G" & LRColA + 2) = Application.CountIfs(rg.Range("AJ1:AJ" & aLastRow), ws1.Range("B" & LRColA + 2), _
rg.Range("AC1:AC" & aLastRow), "U" Or "N" Or "R" Or "L" Or "P" Or "O")
For context, the macro above first finds the last entry made on Column A, From there, it determines the contents of what's in Column B 2 rows below it. Then it uses that as the criteria for my CountIf statement.
This works when I have multiple criteria with different Range. But now I need to count the same range AC1 through end row AC. The contents have 17 different "statuses". I only want to count if the status is "U,N,R,L,P,O" not the rest. I'm stumped...
any help in the right direction would be appreciated.
(Edit: I should probably say that I know I can just add the countifs together using countif statement and "U" + countif statement and "N" + countif statement and "R" Etc. But that's extremely clunky. I'd rather find an easier, less wordy version of doing this)
Last edited: