1

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)))
Improve this question

1 Answer 1

Reset to default
4

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 :

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

5 Comments

Yeah, otherwise you would need to get the cell.address to do the string representation of the range
Yeah, but that would be a little... overkill? :D
Perfect, thank you! Now, as a last question: How to I determine to beginning and ending row of searchRngQue? .end(xlup/down) does not to the trick...
searchRngQue.Cells(1,1).Row would be the first and searchRngQue.Rows.Count the last.
@Damian it certainly would but I just wanted to give the context for the OP to hopefully grasp the difference

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.