finding sum of squares of column using for each

Question

I am a newbie. I want to sum the squares of the numbers in the active column. I am getting an error 'object doesn't support this method'. Here is my code

Sub sum_squares()
Dim total As Integer
Dim c As Range
Dim d As Range

'set d equal to column from active cell down to last non-empty'
d = Range(ActiveCell, ActiveCell.endxldown)
total = 0
For Each c In d
   total = total + c.Value ^ 2
Next c

End Sub

Appreciate the help.

Thanks

`.End(xlDown)` - `endxldown` is incorrect. – BigBen Aug 01 '18 at 13:17 — BigBen, Aug 01 '18 at 13:17

Darren Bartrup-Cook · Answer 1 · 2018-08-01T14:05:21.927

As has been pointed out you've got the syntax of xlDown incorrect. You should also start at the bottom and move up - xlDown may not find the last cell.

E.g.

With a value in cell A1:A3 and A1 as the ActiveCell it will correctly return A3.
Same scenario but with A4 left blank and a value in A5 still returns A3.
Same scenario with A1 left blank it returns A2.

This will return a reference from the ActiveCell to the last cell containing a value in that column.
Note that if the ActiveCell is lower down than the last cell containing data you'll get a reference reflecting that.

Set d = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))

A Range can be made up of one or more cell references:

Range("A1") and Range("A1,A3,C5") reference individual cells.
Range("A1:C5") and Range("A1", "C5") reference all cells between the first and last address.

A Cell is a single cell reference that uses row and columns identifiers.

Cells("A1") will return an error as it's a full address.
Cells(1,"A") will return A1 (row 1, column A)
Cells(1,1) will also return A1 (row 1, column 1)

The code above is using two cell addresses to reference all cells between the two.

Range(ActiveCell,....) is the reference to the first cell.
Cells(Rows.Count, ActiveCell.Column) is the reference to the second cell using row numbers and column numbers.
If the ActiveCell is in column B then this is the same as writing Cells(1048573,2).
The End(xlUp) then goes from that cell back up to the first one containing data.

Please can you explain the code there? Specifically Cells(Rows.Count, ActiveCell.Column) — Napolean, Aug 01 '18 at 13:47
I see so rows.count refers to the last row of excel. Thank you. — Napolean, Aug 01 '18 at 14:10
Yes. You can hard code the number if you prefer as long as you're not going to be using it on an Excel 2003 or less version - it will throw an error with less rows. You might also want to look into avoiding `ActiveCell` and `Select` in VBA by fully qualifying your range references [How to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Darren Bartrup-Cook, Aug 01 '18 at 14:14

score 0 · Answer 2 · answered Aug 01 '18 at 13:23

0

There is a syntax error in your code - .endxldown and add Set before assigning range.

Correct it to - Set d = Range(ActiveCell, ActiveCell.End(xlDown)

answered Aug 01 '18 at 13:23

nagarajannd

715
5
11

thank you so much. So is d an object because it does not change whearas c is a variable because its what we're looping through? – Napolean Aug 01 '18 at 13:40
Both `c` and `d` are variables referencing objects. `c` holds a reference to a single cell within the range of `d`. – Darren Bartrup-Cook Aug 01 '18 at 13:46
so why does d need a 'set' but not c? – Napolean Aug 01 '18 at 13:48
I knew you were going to ask that next which is why I nearly didn't add the first comment. I honestly don't know except to say that `For Each Set C in D` doesn't sound right. – Darren Bartrup-Cook Aug 01 '18 at 14:09

finding sum of squares of column using for each

2 Answers2