VBA & Macro Problems

VBA Code to Insert Formulas Without Overwriting Existing Excel Data

WPS Content Manager Oct 12, 2026 868 views

Question details

The user needs a VBA macro to copy formulas from a header row (B2:K2) down to rows 3 through 1200. The macro must skip rows that already contain data to avoid overwriting them, while ensuring that the formula's relative references remain mathematically correct for the newly inserted rows.

How to Insert Formulas Without Overwriting Existing Data Using VBA

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating the population of formulas across a large dataset where some rows already contain manual entries or historical data.
Observed behavior: Standard copy-paste macros either overwrite existing values or, when skipping occupied rows, insert formulas with incorrect relative cell references that point to the wrong rows.

Before you start

Ensure the Developer tab is enabled in your ribbon via the Customize Ribbon settings, and note the exact column range (e.g., B to K) so you can correctly specify the offset and range dimensions in your VBA loop.

Solution 1Recommended

Use a VBA Loop with an Emptiness Check and R1C1 References

By iterating through each row with a loop and applying formulas using the R1C1 reference style, you can skip occupied rows and ensure relative references perfectly adapt to the current row.

Using a simple AutoFill command in VBA will typically overwrite existing data. To bypass this, a For...Next loop is required to evaluate each destination row individually. Using the FormulaR1C1 property instead of the standard Formula property guarantees that when a formula is placed in row 10 after skipping row 9, it references row 10 correctly.

Open the VBA Editor

Navigate to the Developer tab on the ribbon and click on 'Visual Basic', or press the Alt + F11 shortcut on your keyboard to open the VBA Editor.

Insert a New Module

In the Project Explorer pane on the left, right-click on your workbook's name, select 'Insert', and click on 'Module' to create a blank scripting window.

Write the Evaluation Loop

Create a For loop (e.g., For i = 3 To 1200). Inside the loop, use an If statement like 'If WorksheetFunction.CountA(Range("B" & i & ":K" & i)) = 0 Then' to check if the target range in that row is completely empty.

Apply Formulas Using R1C1

Inside the If statement, assign the formula to the empty range using the .FormulaR1C1 property. For example: Range("B" & i & ":K" & i).FormulaR1C1 = Range("B2:K2").FormulaR1C1.

Run the Macro

Click anywhere inside your newly written code and press F5, or click the green 'Run Sub/UserForm' button in the toolbar to execute the macro.

Use a VBA Loop with an Emptiness Check and R1C1 References

Testing on Sample Data: Always test macro loops on a sample worksheet first to ensure your specific relative references behave as expected before applying it to your 1200-row production dataset.

Solution 2

Use GoTo Special to Fill Blanks Without VBA

If you prefer not to use VBA, you can utilize the native GoTo Special feature to target only empty cells and apply your formula simultaneously.

Seamless VBA Automation

Automate Data Tasks Effectively with WPS Spreadsheet

WPS Office provides robust built-in support for VBA macros, allowing you to easily execute complex looping scripts to populate formulas without losing existing data. The developer environment mirrors what you are already used to.

1. Enable Developer Tools: Open WPS Spreadsheet, go to the Options menu to customize your ribbon, and check the 'Developer' tab to make it visible.
2. Open the VB Editor: Click the 'Developer' tab and select 'VB Editor' to access the coding environment.
3. Insert Your Code: Add a new module and paste your For...Next loop script designed to skip occupied rows.
4. Run Safely: Execute the macro directly on your spreadsheet to instantly populate formulas while preserving your valuable manual data entries.

Full VBA support for advanced macros and looping tasks.Complete format compatibility with Microsoft Excel (.xlsx, .xls, .xlsm).Familiar Developer tab and VB Editor interface for a zero-learning-curve transition.Lightweight performance, handling thousands of rows without freezing.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my relative references point to the wrong rows when skipping rows in VBA?

When standard copy-paste methods in VBA skip rows, the formula often statically increments based on the source rather than the destination. Using the '.FormulaR1C1' property ensures the formula is defined by its spatial relationship (e.g., 'same row, two columns left'), keeping references completely accurate regardless of how many rows are skipped.

Can I hide the base formula row after running the macro?

Yes. If your formulas in row 2 are only used as a template, you can add 'Rows(2).Hidden = True' at the end of your VBA script to keep the spreadsheet tidy.

Does WPS Spreadsheet support Excel VBA macros (.xlsm files)?

Yes, WPS Office fully supports running and editing standard VBA macros. You can open an .xlsm file directly in WPS Spreadsheet, access the VB Editor, and execute the scripts just as you would in Microsoft Excel.

Excel & Data Problems