VBA & Macro Problems

How to Create a VBA Macro to Copy and Increment Excel Formulas in Blocks

Camila Milosovich Oct 12, 2026 869 views

Question details

The user needs to create a VBA macro that copies a formula across repeating blocks of rows while dynamically incrementing the referenced row number (e.g., changing BI$25 to BI$26 for the next block).

How to Copy and Increment Excel Formulas in Blocks Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Automating the setup of a massive spreadsheet requiring formulas to be filled over thousands of rows, where absolute references need to increment programmatically per block.
Observed behavior: Without a macro, copying the formula requires manual updating of the row references block by block, which is highly inefficient for 1,975 repetitions.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon to access the VBA Editor.

Solution 1Recommended

Use a VBA For-Next Loop to Increment Block Formulas

Write a VBA script utilizing a loop to iterate through your target blocks, assigning the base formula and dynamically incrementing the row reference variable.

By utilizing a VBA loop, you can systematically navigate down your spreadsheet and apply formulas in distinct blocks. Instead of manually adjusting the absolute row reference, the script uses string concatenation to update the reference dynamically based on the loop counter.

This method seamlessly processes thousands of blocks in a matter of seconds, fully automating repetitive structural data formatting.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications Editor.

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank script window.

Declare Macro Variables

Write your subroutine and declare variables for the loop counter, block size, and starting reference row (e.g., Dim i As Long, Dim refRow As Long).

Construct the Loop and Formula String

Create a For-Next loop that increments through the dataset. Inside the loop, assign the formula using string concatenation, such as: Range("A" & i).Formula = "=BI$" & refRow.

Increment Reference and Run

Add a line inside the loop to increase the refRow variable (refRow = refRow + 1). Press F5 to execute the macro and populate your repeating blocks.

Use a VBA For-Next Loop to Increment Block Formulas

Mass Automation Achieved: This loop approach can comfortably handle over 1,975 repetitions, completing the formula assignment flawlessly without manual intervention.

Advanced Spreadsheet Editor

Automate Complex Workflows with WPS Spreadsheet

WPS Spreadsheet offers robust built-in support for VBA macros. You can seamlessly write, edit, and execute automated tasks to quickly increment formulas and process large datasets efficiently.

1. Download and Install WPS Office: Get the free WPS Office suite from the official website and open your Excel Macro-Enabled workbook.
2. Access Developer Tools: Click on the Developer tab in the top ribbon to access the built-in Macro and Visual Basic tools.
3. Run Your VBA Macro: Click 'Macros' to select your block-incrementing script or open the VBA Editor to make quick adjustments, then hit Run.

Full support for VBA macros to automate repetitive spreadsheet tasks.Complete format compatibility with Microsoft Excel (.xlsx and .xlsm) files.Lightweight architecture ensures fast performance even on large datasets.Clean, user-friendly interface that feels familiar immediately.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA formula injection result in a #NAME? error?

This usually happens when the variable is accidentally included inside the formula's quotation marks. Ensure you are using proper string concatenation in VBA (e.g., "=BI$" & refRow) so Excel reads the actual row number rather than the text 'refRow'.

Can I undo the changes made by a VBA macro?

No, actions executed via a VBA macro cannot be undone using the standard Undo button (Ctrl + Z). It is highly recommended to save a copy of your workbook before running new or untested scripts.

Will my VBA macro be saved if I use a standard .xlsx format?

No, standard .xlsx files cannot store VBA code. You must select 'Save As' and choose the Excel Macro-Enabled Workbook (.xlsm) format to preserve your macro.

Does WPS Office support VBA for running these types of loops?

Yes, WPS Spreadsheet has excellent support for VBA macros. You can open the VBA Editor, insert standard For-Next loops, and interact with cell formulas exactly as you would in Microsoft Excel.

Excel & Data Problems