countif outputting "true" or "false" rather than number vba

Question

I have been trying to code a countif function into a loop, however, I am having a little trouble with the outputs. Instead of reading a number when the computation occurs, the function keeps outputting "true" or "false". Maybe there is an error in my code, but I have used many countif functions in the past without experiencing a problem such as this. As you can see below, I tried to write the function in two different ways, but both either didn't work or outputted "true" or "false".

Please Help.

Sub CorrectSets()

Dim Cell As Range

Range("B100000").End(xlUp).Select
LastRow = ActiveCell.Row

For Each Cell In Range("S2:S" & LastRow)
    StartTime = Cell.Offset(0, -12)
    Shift = Cell.Offset(0, -14)
    SortedOp = Cell.Offset(0, -17)
    DOW = Cell.Offset(0, -5)
    'Cell.Value = CountIF(E2:E & LastRow, Shift, N2:N & LastRow ,DOW, B2:B & LastRow,SortedOp, G2:G & LastRow, " < " & StartTime)
    Cell.Value = "=CountIF(E2:E" & LastRow & ", " & Shift & ", N2:N" & LastRow & "," & DOW & ",     B2:B" & LastRow & "," & SortedOp & ", G2:G" & LastRow & ", " < " " & StartTime & ")"
Next Cell

Are you sure you don't want to be using the `COUNTIFS` function rather than the `COUNTIF` function given the number of arguments you're passing? — Gareth, Jan 12 '15 at 15:50
@ksmit144 File -> Options -> Advanced -> *scroll all the way down to* Lotus Compatibility and uncheck any boxes (*note the last 2 sections are related to Lotus compatibility*) — , Jan 12 '15 at 16:29
Please post the output of the string you are using for the formula. — sancho.s ReinstateMonicaCellio, Jan 12 '15 at 17:43

score 2 · Accepted Answer · answered Jan 12 '15 at 16:05

2

If you want to put a countif() Formula in Cell then:

Cell.Formula = "=CountIF(E2:E &...............

If you want to put the formula's result in Cell then:

Cell.Value = Application.Worksheetfunction.CountIF(E2:E &....................

answered Jan 12 '15 at 16:05

Gary's Student

95,722
10
59
99

There is also `Application.Evaluate("COUNTIFS(...)")` but I don't recommend it without specifying the worksheet names in the cell and cell range addresses. – Jan 12 '15 at 16:08
@Jeeped ...I agree with you.....it is a lot of work getting the exact string for *Evaluate()* – Gary's Student Jan 12 '15 at 16:15
@Gary's Student I tried to change the formula to `Cell.Value = WorksheetFunction.CountIfs(E2:E" & LastRow & ", " = " " & Shift & ", N2:N" & LastRow & "," = " " & DOW & ", B2:B" & LastRow & "," = " " & SortedOp & ", G2:G" & LastRow & ", " < " " & StartTime & ")` but it is now saying that it is expecting parentheses where it shouldn't. – ksmit144 Jan 12 '15 at 16:33
@Jeeped - if all ranges are on one sheet then you can use (e.g.) `Sheet1.Evaluate()` - that will evaluate in the context of `Sheet1` – Tim Williams Jan 12 '15 at 17:28
@ksmit144 - If using `WorksheetFunction.CountIfs`, then you have to use `Range` objects as arguments, not simply `"E2:E"...` (plus, you forgot double quotes). See [official documentation](http://msdn.microsoft.com/en-us/library/office/ff196714%28v=office.15%29.aspx). – sancho.s ReinstateMonicaCellio Jan 12 '15 at 17:41

score 1 · Answer 2 · edited May 23 '17 at 10:25

You should use

Cell.Formula = "=CountIFs..."

or

Cell.Value = WorksheetFunction.CountIfs...

See official documentation.

Plus:

To find the last row containing data in a column (B in this case) use
```
Dim ws as Worksheet
Set ws = ActiveSheet
Dim LastRow as Long
LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
```
ws is a reference to the Worksheet of interest (ActiveSheet in my example). See this answer.
You'd rather fully qualify your ranges, and avoid using Select unless it is strictly needed. With the code posted above,
```
Range("B100000").End(xlUp).Select
```
might not be needed.
If using Cell.Formula = "=CountIFs...", it might be convenient to use
```
Dim frm as String
frm = "=CountIFs..."
Cell.Formula = frm
```
for easier debugging.

countif outputting "true" or "false" rather than number vba

2 Answers2