VBA & Macro Problems

How to Use VBA to Increase Row Numbers in Excel Formulas

Olivia Miller Oct 16, 2026 870 views

Question details

The user needs to bulk update external-reference formulas across multiple worksheets by adding a fixed amount, such as 12, to the row numbers within those formulas.

How to Use VBA to Increase Row Numbers in Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Using a VBA macro to modify formulas across hundreds of worksheets, but encountering syntax errors and logical issues when referencing varying row lengths.
Observed behavior: The macro fails to execute properly because the user is incorrectly using text strings like Cells('Col') instead of numeric indices, and the string manipulation fails when row numbers range from one to five digits.

Before you start

Before running any VBA macro to modify formulas in bulk, ensure you create a backup copy of your workbook, as macro actions cannot be undone using the standard Excel Undo feature.

Solution 1

Correct the VBA Cells Syntax and Best Practices

Learn the proper way to reference cells using row and column index numbers to prevent script errors.

The primary reason the script fails is due to incorrect usage of the Cells property. In VBA, the Cells property strictly requires the row number first, followed by the column number: Cells(RowIndex, ColumnIndex).

Using strings like 'Col' or 'Row' or leaving arguments blank will result in runtime errors. Additionally, it is highly recommended to avoid the Select and Selection methods, as they significantly slow down the macro and are prone to errors.

Identify correct numerical indices

Determine the exact numeric row and column you want to target. For example, to target cell C3, use Cells(3, 3).

Remove string references

Locate invalid references in your code, such as A = Cells('Col',), and replace them with standard integer variables like X and Y in the format Cells(X, Y).

Replace Selection with direct references

Instead of using Range('B9').Select followed by Selection, perform actions directly on the object, like Range('B9').Replace.

Solution 2Recommended

Use a Dynamic VBA Loop with Regular Expressions

Implement a VBA macro utilizing Regular Expressions to dynamically identify and increase row numbers of any digit length within your formulas.

Advanced Spreadsheet Capabilities

Run Macros and Manage Formulas effortlessly with WPS Spreadsheets

WPS Office provides a highly compatible spreadsheet environment that seamlessly handles complex external-reference formulas and supports advanced macro execution to automate bulk data modifications.

1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
2. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm or .xls file containing the external references.
3. Access Developer Tools: Navigate to the Developer tab to manage macros, edit your VBA scripts, and automate your formula updates smoothly.

Fully compatible with Microsoft Excel formats, including .xlsx, .xls, and macro-enabled .xlsm files.Lightweight, fast installation with a familiar ribbon interface for zero learning curve.Powerful built-in formula auditing tools and dynamic array support.

microsoft office alternative - wps office

Frequently Asked Questions

What is the correct syntax for the Cells function in VBA?

The correct syntax is Cells(RowIndex, ColumnIndex), where both arguments are numbers. For example, Cells(3, 4) refers to row 3, column 4 (which is cell D3). You cannot use strings like Cells('Row') or Cells('Col').

How can I update a formula in VBA without using the Select method?

You can modify a formula directly by referencing the range object's property. For example, use Range('B9').Formula = '=YourNewFormula' instead of selecting the cell first. This approach is significantly faster and prevents screen flickering.

How do I handle varying row number lengths in VBA when parsing formulas?

Using VBScript Regular Expressions (RegExp) allows you to dynamically identify and extract the numeric part of a cell reference, successfully parsing the formula regardless of whether the row number is one, three, or five digits long.

Why does my macro run slowly when updating hundreds of worksheets?

Macros run slowly if they update the screen or calculate formulas after every change. To speed up execution, add Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual at the start of your code, and restore them to True and xlCalculationAutomatic at the end.

Excel & Data Problems