VBA & Macro Problems

How to Fix VBA For Loop Writing Sum to Every Row in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a VBA macro to group mileage rows based on Boolean values in column B and output the sum to column F, but only on the first row of each group rather than every row.

Product: Excel
Device & OS: not provided
Scenario: Grouping and summing mileage records using a VBA macro based on Boolean conditions in a specific column.
Observed behavior: The current VBA code incorrectly calculates and writes the running total to every row in the group instead of placing it solely on the first row of each respective group.

Before you start

Before editing your VBA code, ensure you have saved a copy of your workbook as a Macro-Enabled Workbook (.xlsm) to prevent any accidental loss of data if the loop causes an infinite run.

Solution 1Recommended

Modify the VBA Loop to Track the First Row of Each Group

Correct the logic in your For Loop to keep track of the start of a group and use dynamic Range references to compare current and next rows.

When looping through rows to sum grouped values, the key is to track the starting row index of each group. Instead of writing the running total on every iteration, you must only write the total when the group changes or at the end of a block.

Additionally, avoid using static square-bracket expressions (like [B2]) inside loops. Using Range("B" & ct) allows you to reference cells dynamically based on your loop counter, which is essential for iterating through datasets.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor and locate the module containing your existing loop macro.

Declare Tracking Variables

Set up variables at the beginning of your code to track the first row of the current group (e.g., Dim firstRow as Long) and the running sum (e.g., Dim groupSum as Double).

Implement Dynamic Comparisons

Inside your For loop (using variable ct), use an If statement with dynamic references to check if the current row's Boolean value matches the next row: If Range("B" & ct).Value = Range("B" & ct + 1).Value Then.

Accumulate and Write the Sum

Add the mileage to groupSum. When the Boolean value changes (the Else part of the statement), add the final value, write groupSum to Range("F" & firstRow).Value, and reset groupSum and firstRow for the next group.

Dynamic Cell References: Using Range or Cells allows concatenation with your loop variable (e.g., Range("B" & ct)), which is required for dynamic row evaluation. Hardcoded bracket syntax cannot adapt to loop variables.

WPS Spreadsheet VBA Support

Run and Edit VBA Macros Seamlessly with WPS Office

WPS Office provides robust support for VBA and macros, allowing you to run, edit, and debug your VBA For Loops without switching environments. Enjoy an interface highly compatible with standard Developer tools.

1. Open your Macro Workbook: Download and install WPS Office, then open your .xlsm file in WPS Spreadsheet.
2. Access the Developer Tools: Go to the 'Developer' tab on the top ribbon and click 'Visual Basic' to launch the built-in VBA Editor.
3. Edit the Loop Code: Paste or adjust your For Loop logic using standard VBA syntax to fix the grouping behavior.
4. Run the Macro: Click the 'Run' button directly in the editor to execute your script and properly sum your boolean groups.

Highly compatible with Microsoft Excel VBA files (.xlsm, .xlsb)Familiar Developer tab and Macro Editor interfaceLightweight application that processes heavy macro loops quicklyCost-effective spreadsheet solution for advanced data grouping

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro writing the sum to every row in the group?

This happens if your output statement (writing to Column F) is placed directly inside the loop's active iteration without conditionally checking if it is the first or last row of the group. You need to wrap the output statement in logic that only executes when the group changes.

How do I dynamically reference cells within a VBA For Loop?

Avoid using shorthand bracket notation like [A1]. Instead, use the Range or Cells object combined with your loop variable using the ampersand operator, such as Range("B" & i) or Cells(i, 2).

Can I run my existing Excel VBA scripts in WPS Office?

Yes, WPS Office supports VBA. You can open Macro-Enabled Workbooks (.xlsm) and access the built-in Visual Basic editor to run, debug, or modify your existing macros with excellent compatibility.

Excel & Data Problems