CODEWITHSUNDEEP
excelvbasyntax-error
user14296107
user14296107

Reputation: 15

VBA Sum Dynamic Range Issue

The following line of code gets the Application or user defined error now despite it executing fine before:

sum_range = Application.Sum(ThisWorkbook.Worksheets("Shifting").Range(Cells(20, col), Cells(rw - 1, col)))

What should I change for it to sum the dynamic range correctly with out the error?

EDIT:

Error in the syntax, col was assigned 0 and therefore the error. Solved below.

Upvotes: 0

Views: 50

Answers (1)

Variatus
Variatus

Reputation: 14383

The most common reason for the error you describe is that a number < 1 is specified as row or column. Hence the question of @Foxfire. However, in your bit of code the absence of qualification of the cells is standing out. They aren't automatically on the same sheet as the range. In fact, Excel works with the opposite logic.

Since you say that the code used to work before and doesn't work now the reason might be that you now have a different sheet active when you call the code. Modify your code to specify the sheet. Observe the leading periods that connect each cell address to the With statement instead of to the ActiveSheet.

With ThisWorkbook.Worksheets("Shifting")
    sum_range = Application.Sum(.Range(.Cells(20, col), .Cells(rw - 1, col)))
End With

Upvotes: 1

Related Questions