What's unique about row and column ranges compared to vanilla ranges?
1. This works (it calculates the deviation of each column in a range):
Code
Sub test()
Const nRow As Long = 5
Const nCol As Long = 5
Dim iCol As Long
Dim r As Excel. Range
Dim Func(1 To nCol) As Double
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Set r = Range("A1").Resize(nRow, nCol)
For iCol = 1 To nCol
Func(iCol) = WF.StDev(WF.Index(r, 0, iCol))
Next iCol
End Sub
Display More
This doesn't:
Code
Sub test()
Const nRow As Long = 5
Const nCol As Long = 5
Dim iCol As Long
Dim r As Excel.Range
Dim rCol As Excel.Range
Dim Func(1 To nCol) As Double
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Set r = Range("A1").Resize(nRow, nCol)
For Each rCol In r.Columns
Func(iCol) = WF.StDev(rCol.Value)
Next rCol
End Sub
Display More
2. In the second example, rCol.Count (the general property that returns the count of members in a collection, of which a range are one) returns 1; rCol.Cells.Count of course returns 5.
3. You can't index a row or column range; e.g., rCol(1) errors -- you have to do rCol.Cells(1).
Whassup with that?