Countif Function in VBA with Dynamic Criteria

Question

I am trying write a CountIF formula that uses a dynamic criteria to count from a fixed range. Below is the code I was trying to make work:

Dim Flow As String

j = 5
For i = 1 To 13
    Flow = Cells(2, j)
    Cells(6, j).Formula = "=COUNTIF($E50:$E100,Flow)"
    j = j + 2     
Next i

The results pastes =COUNTIF($E50:$E100,Flow) in the targeted cells instead of =Countif($E50:$E100,E2), =Countif($E50:$E100,G2), and so on.

`"=COUNTIF($E50:$E100," & Flow & ")"` (this is probably a duplicate of over 100 questions) — John Coleman, Apr 28 '20 at 22:46
You should change your declaration of flow to: `Flow = Cells(2, j).value` — Luuklag, Apr 29 '20 at 06:32

score 2 · Answer 1 · answered Apr 29 '20 at 00:41

2

Thank You Johanness. Because the lookup criteria had to be string. I just had to make a slight tweak.

Cells(6, j).Formula = "=COUNTIF($E50:$E100,""" & Flow & """)"

This seems to do it. Cheers! Appreciate the help

answered Apr 29 '20 at 00:41

AbsoluteSanjeev

score 1 · Answer 2 · answered Apr 28 '20 at 22:46

1

try

Cells(6, j).Formula = "=COUNTIF($E50:$E100," & Flow & ")"

answered Apr 28 '20 at 22:46