Fix Excel VBA Error 424 When Using COUNTIF on a Cell
Question details
Learn why VBA raises Error 424 when COUNTIF receives a cell value instead of a Range, and how to test whether cells contain numeric values.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A VBA loop checks values in column B and needs to identify numeric cells.
- Observed behavior
- Using ws.Cells(i, "B").Value with COUNTIF passes a value rather than a Range object, which causes an Object required error.
Open the VBA editor with Alt+F11 and locate the procedure that checks column B. Confirm whether you need to accept all numeric values or exclude values such as dates, times, Boolean values, blanks, or error cells.
Use IsNumeric to test the cell value directly
Use this approach when your goal is simply to determine whether the value in a cell can be treated as numeric.
IsNumeric evaluates a value, so it is the simpler choice when no COUNTIF criterion is needed. Be aware that its result can include some values, such as times or Boolean values, that may not match your business rule.
In Excel, press Alt+F11 to open the Visual Basic Editor, then open the module containing the loop that checks ws.Cells(i, "B").
Replace the COUNTIF expression with a direct value check: If IsNumeric(ws.Cells(i, 2).Value) Then.
In the Visual Basic Editor, place the cursor in the procedure and press F5, then test numeric values, text, blanks, dates, and error cells in column B.

Pass the cell Range to COUNTIF instead of its Value
Use this method when COUNTIF is required because you need worksheet-style criteria rather than a basic numeric test.
Use WPS Spreadsheet for compatible workbook editing and calculations
WPS Office is a free, lightweight Office alternative for opening, editing, and reviewing Excel workbooks. Its familiar spreadsheet interface and strong format compatibility make it easy to move everyday calculation and data-checking work from Microsoft Office.

Frequently Asked Questions
Why does COUNTIF cause VBA Error 424 when I use Cells(i, "B").Value?
Cells(i, "B").Value returns the cell's value, while COUNTIF requires a Range as its first argument. Pass ws.Cells(i, 2) without .Value, or use IsNumeric when testing the value itself.
What is the simplest VBA test for a numeric cell?
Use If IsNumeric(ws.Cells(i, 2).Value) Then. This checks the value stored in the cell rather than asking COUNTIF to evaluate a one-cell range.
Does IsNumeric return True for dates and times?
It can return True for some date and time values because Excel stores them as serial numbers. Use IsDate first if dates must be excluded from the numeric result.
How can I avoid treating blank cells or errors as numbers in VBA?
Test special cases before IsNumeric, for example: If Not IsError(cell.Value) And Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then. Add IsDate or VarType checks when your rule also excludes dates or Boolean values.




