CODEWITHSUNDEEP
excelvbafunction
Ardnic
Ardnic

Reputation: 51

Build-in Excel function not working in VBA code - What is my mistake?

I am trying to sum up all values of a predefined range of cells. However, that range has been defined by rows only (e.g. searchRngQue = Rows 5 to 10). I now tried the following to extract the row numbers from the range and add the relevant Column to define the exact cells from which the sum should be calculated. However the code gives me the Run-time Error 1004: "Application-defined or object-defined error"

Another problem seems to be that the .End(xlUp/xlDown) methods are not returning the first/last row of the range. What should I use instead? Can anyone show me what I am missing?! Thanks a lot in advance!

sumQue = Application.WorksheetFunction.Sum(.Range(.Cells(searchRngQue.End(xlUp).Row, 8) & ":" & .Cells(searchRngQue.End(xlDown).Row, 8)))

Upvotes: 1

Views: 68

Answers (1)

Damian
Damian

Reputation: 5174

You meant to do this:

sumQue = Application.WorksheetFunction.Sum(.Range(.Cells(searchRngQue.End(xlUp).Row, 8), .Cells(searchRngQue.End(xlDown).Row, 8)))

When working with Range(Cells(1,1), Cells(100,1)) you use a , as separator, not :

Upvotes: 4

Related Questions