Excel Error Codes

How to Fix VBA Runtime Error 424 Object Required in a For Each Loop in Excel

Muhammad Talha Sep 28, 2026 871 views

Question details

The user is encountering runtime error 424 'Object required' when running a VBA function that iterates through a collection in a For Each loop.

How to Fix VBA Error 424 Object Required in a For Each Loop

Product: Microsoft Excel (VBA/Macros)
Device & OS: not provided
Scenario: Creating summary tables using a VBA macro from unique string values and numeric columns.
Observed behavior: The macro execution abruptly stops, throwing a 'Runtime error 424: Object required', specifically failing at the 'For Each cell In uniqueValues' line.

Before you start

Before modifying your VBA script, ensure you have opened the VBA Editor (ALT + F11) and noted the exact line highlighted in yellow where the debugger stopped the code execution.

Solution 1Recommended

Change the Loop Variable to a Variant Type

Modify the variable type declared in your 'For Each' loop to match the primitive data type stored in your VBA collection.

When you add items to a collection using '.Add cell.Value', you are storing the actual data values (like text strings or numbers), not the Excel 'Range' objects themselves. If your loop variable is declared as a 'Range', VBA will throw Error 424 because it expects an object reference, but receives a primitive value from the collection.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor, then find your macro in the Project Explorer.

Locate the Variable Declaration

Find the line of code where you declared the loop variable, which currently looks like 'Dim cell As Range'.

Update the Variable Type

Change the declaration from 'Range' to 'Variant' so it can handle primitive values. For example, change it to 'Dim item As Variant'.

Modify the For Each Loop

Update your loop statement to use the newly declared Variant variable. Change 'For Each cell In uniqueValues' to 'For Each item In uniqueValues'.

Change the Loop Variable to a Variant Type

Check Data Types: Using a Variant is the safest and most flexible approach when iterating over a custom collection of mixed or primitive values in VBA.

Solution 2

Store Range Objects Instead of Values in the Collection

Adjust your collection insertion method to store actual Range objects if you specifically need to access cell properties like formatting or addresses later in the loop.

Free Microsoft Office alternative

Experience Seamless Spreadsheet Management with WPS Office

Tired of dealing with complicated Excel VBA errors? WPS Office offers a lightweight, free alternative to Microsoft Office. With high compatibility for Excel files and intuitive data processing tools, you can manage your data easily without constantly relying on complex VBA scripts.

1. Download the Installer: Visit the official WPS Office website and click 'Download' to get the free installation package.
2. Install WPS Office: Run the downloaded installer and follow the quick on-screen instructions to set up WPS Office on your device.
3. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbooks seamlessly to continue your data analysis.

Highly compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Lightweight architecture ensuring fast installation and smooth operationFamiliar user interface for immediate productivity with zero learning curveAdvanced built-in data analysis tools to replace complex manual coding

microsoft office alternative - wps office

Frequently Asked Questions

What does Runtime Error 424 'Object required' mean in VBA?

This error occurs when VBA expects an object reference (like a Worksheet, Range, or Workbook) but receives a non-object value (like a string, integer, or null), or when you attempt to assign an object to a variable without using the 'Set' keyword.

Can I use 'Set' to fix Error 424 in this specific scenario?

No, not in this specific case. While missing the 'Set' keyword is a common cause for Error 424, in a 'For Each' loop iterating over primitive string or numeric values stored in a collection, you cannot use 'Set'. You must change the variable type to Variant instead.

Why does adding 'CStr(cell.Value)' to a collection cause issues?

The 'CStr(cell.Value)' part is actually the key for the collection item, which is perfectly valid. The issue arises from the first argument (cell.Value), which inserts a primitive value instead of a Range object into the collection, creating a mismatch when you later try to loop through it using a Range variable.

Does WPS Office support VBA macros?

Yes, WPS Office provides support for VBA macros in its specific business versions or via an optional VBA module download, allowing you to run, edit, and debug macro-enabled workbooks (.xlsm) smoothly.

Microsoft Office Error Codes