CODEWITHSUNDEEP
vbaexcel
Kash
Kash

Reputation: 21

Excel VBA: Summing rows and columns

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!

Upvotes: 2

Views: 524

Answers (1)

YowE3K
YowE3K

Reputation: 23994

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

Upvotes: 2

Related Questions