# VBA Countif less than specific time

#### 30percent

##### Board Regular
Hi,

I have the following formula on a cell and it gives me number 78.

=COUNTIF(Y2:Y505,"<"&TIME(0,2,0))

However, when I tried to code that formula using VBA it returned a different number instead. Wonder if someone could point out what I did wrong.

Code:
``````finalRow = ThisWorkbook.Worksheets("Paste").Cells(Sheets("Paste").Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Worksheets("Summary").Range("B7").Formula = WorksheetFunction.CountIf(ThisWorkbook.Worksheets("Paste").Range("Y2:Y" & finalRow), "<" & Time(0, 2, 0))``````

I'm trying to count the number of rows that have time less than 2 mins.

Thank you!

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Yongle

##### Well-known Member
Code:
``& Time(0, 2, 0)``

Try:
Code:
``& TimeValue("00:02:00")``

Last edited:

Replies
2
Views
36
Replies
3
Views
84
Replies
0
Views
41
Replies
1
Views
59
Replies
16
Views
129

1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...