Excel VBA: Summing rows and columns

Question

Very new to VBA and working on a project. I have a file that contains sales data for 1000 products (rows) across 50 days (columns).

In the last column, I want to display the total sums for each product across each day. What is the best way to accomplish this? Should I do a for-next loop?

This is what I have so far:

Sub testsum()

Worksheets("Sheet1").Range("BJ1") = "Sum"

Range("BJ2:BJ1001").Value = Application.WorksheetFunction.Sum(Range("B2:BI1001"))

End Sub

I know this isn't right because I am getting the sum of sales for all 1000 products for 50 days vs. only getting the sum of each product (Row) for all days.

Hope I wasn't confusing in explaining. Thanks!

Have you tried using Pivot Tables? Or even normal tables, just put the formula to sum each row product in the last column, and every time you insert a new day, the formula will update — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jan 22, 2018 at 1:09

YowE3K · Accepted Answer · 2018-01-21 22:21:08Z

2

You can either loop through all 1000 rows, or you can use an Excel formula to do the calculation and then set the value to what was calculated.

Method using a formula:

Sub testsum()    
    Worksheets("Sheet1").Range("BJ1") = "Sum"

    With Worksheets("Sheet1").Range("BJ2:BJ1001")
        .Formula = "=SUM(B2:BI2)"
        .Value = .Value
    End With
End Sub

answered Jan 21, 2018 at 22:21

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Summing rows and columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related