CODEWITHSUNDEEP
vbaexcel
Napolean
Napolean

Reputation: 9

finding sum of squares of column using for each

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

Upvotes: 0

Views: 370

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

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.

Upvotes: 1

nagarajannd
nagarajannd

Reputation: 715

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)

Upvotes: 0

Related Questions