CODEWITHSUNDEEP
excelvba
Smithfield
Smithfield

Reputation: 11

WorksheetFunction Sum of a range outputs 0

I do some calculations before displaying.

The "sum" function outputs 0 despite the values in the range.

sub CompileDashboard()
    For i = 3 To 100
        If Sheets(1).Cells(i, "A").Value = "Week 36" Then
            Sheets(2).Cells(1, 1) = Application.WorksheetFunction.Sum(Range(Cells(i, "AN"), Cells(i, "BF")))
        End If
    Next
End sub

Note: This is a simplification of the step that isn't working.

Upvotes: 1

Views: 636

Answers (3)

user6432984
user6432984

Reputation:

As mentioned in comments this line:

Sheets(2).Cells(1, 1) = Application.WorksheetFunction.Sum(Range(Cells(i, "AN"), Cells(i, "BF")))

Needs to be changed to

Sheets(2).Cells(i, 1) = Application.WorksheetFunction.Sum(Range(Cells(i, "AN"), Cells(i, "BF")))

Just a variation on the theme. I really like this syntax, but you never see it.

Sub CompileDashboard()
    Dim i As Integer
    With Sheets(1)
        For i = 3 To 100
            With .Rows(i)
                If .Columns("A").Value = "Week 36" Then

                    Sheets(2).Cells(i, 1) = Application.Sum(.Columns("AN:BF"))

                End If
            End With
        Next

    End With
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

If you intend to SUM the row (from Sheets(1)) for all columns between "AN" and "BF, then modify your line :

Sheets(2).Cells(1, 1) = Application.WorksheetFunction.Sum(Range(Cells(i, "AN"), Cells(i, "BF")))

to:

Sheets(2).Cells(1, 1) = Application.WorksheetFunction.Sum(Sheets(1).Range("AN" & i & ":BF" & i))

Upvotes: 1

user4039065
user4039065

Reputation:

Always provide explicit .Parent worksheet references for all Range and Range.Cells objects. A With ... End With statement can easily accomplish this and not only cleans up your code but makes it run faster.

sub CompileDashboard()
    with Sheets(1)
        For i = 3 To 100
            If .Cells(i, "A").Value = "Week 36" Then
                 Sheets(2).Cells(1, 1) = Application.Sum(.Range(.Cells(i, "AN"), .Cells(i, "BF")))
            End If
        Next
    end with
End sub

The purpose of your loop is unclear. If there is only one row with "Week 36" then you should be exiting the For ... Next statement once it is found; in fact, a Application.Match(...) would find it instantly. If there are more than one row with "Week 36" then you shouldn't be overwriting the same cell on Sheet2.

Upvotes: 1

Related Questions