Loop using CountIf for text then moving to next column

Question

I have one worksheet (Daily) with data in columns and a header. The data is dynamic based on the number of days in a month and it has been conditionally formatted to icons (Incomplete = T, Complete = R). I'm trying to create a chart on another worksheet (WS2) to show the percent complete and incomplete for each item (header).

I have a table on a 3rd worksheet (WS3) to count all non-empty cells in each column, countif "T", and countif "R". (eg. =COUNTIF(Daily!D5:D29,"R")). The chart on WS1 selects data from WS3 for each item. Sometimes I have to add or delete a column on the Daily page. I'm trying to write a sub to loop through each column on Daily using CountIF and CountA and show the results on WS2.

Sub LoopforCt()

Dim ws As Worksheet
Dim Daily As Worksheet
Dim Test As Worksheet
Dim AllData As Variant
Dim pcv As Range

Set Daily = Sheets("Daily")
Set Test = Sheets("Test")
Set AllData = Daily.Range("B5:B100")
Set pcv = Test.Range("A22")

With AllData
 pcv = "=COUNTIF(Daily!C5:C29,""R"")"
End With

End Sub

I've been able to get the countif to work for the first column, but I'm lost on how to put this in a loop. I want to continue to CountIf for the next column's results in Test.Range("B22"), etc.

Thank you!

Should the formula in B22 be set to "=COUNTIF(Daily!D5:D29,""R"")"? You can write a formula with relative references to an entire range, no need to loop column by column. — BigBen
Thank you BigBen. Yes, the formula in B22 should be as you wrote. Are you suggesting that I have the formula in Excel not in a macro? — RebaS
No, just saying that you don't need to loop through columns - you can write the formula to multiple columns with one line. — BigBen
Ok, I'm thinking something like this? pvc.FormulaR1C1 = "=COUNTIF(Daily!R[-17]C:R[15]C,""R"")" . Would I then add pvc = pvc(0,1) ? — RebaS
pcv should be the entire range - not just A22 but A22:X22 or whatever column. Then pcv.Formula = "=COUNTIF(Daily!C5:C29,""R"")". — BigBen

BigBen BigBen · Accepted Answer · 2019-07-17T03:32:23

You don't need a loop. You can write a formula to a Range in one line. You're using relative references, so the C5:C29 will become D5:D29, E5:E29 as you progress right.

Test.Range("A22:X22").Formula = "=COUNTIF(Daily!C5:C29,""R"")" ' replace X with whatever column you need.

Or if you prefer to keep pcv,

Set pcv = Test.Range("A22:X22")
pcv.Formula = "=COUNTIF(Daily!C5:C29,""R"")"

Loop using CountIf for text then moving to next column

1 Answers