VBA & Macro Problems

Fix Excel VBA Error 424 When Using COUNTIF on a Cell

Elise WilliamsElise Williams Nov 12, 2026 869 views

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.

Fix Excel VBA Error 424 When COUNTIF Checks a Cell
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the affected VBA procedure

In Excel, press Alt+F11 to open the Visual Basic Editor, then open the module containing the loop that checks ws.Cells(i, "B").

2
Replace the COUNTIF condition

Replace the COUNTIF expression with a direct value check: If IsNumeric(ws.Cells(i, 2).Value) Then.

3
Run the macro on sample cells

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.

Use IsNumeric to test the cell value directly
Check special value types: If dates, times, Boolean values, or blank cells must be handled differently, add explicit checks such as IsDate, IsError, IsEmpty, or VarType before relying on IsNumeric.
Free Microsoft Office alternative

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.

Open and edit Excel .xlsx, .xls, and .xlsm workbook formats.Use spreadsheet formulas such as ISNUMBER to check numeric cell content.Work in a familiar ribbon-based spreadsheet interface with a lightweight installation.Share compatible Excel files without changing common worksheet layouts and formulas.
microsoft office alternative - wps 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.