Selecting one column from each row in a table

Question

I have a table structured (Table Name: Table2) like below:

Using VBA, I want to select ONLY a single column value of the current row by iterating over each row. Here is the code and I wrote:

Function findColumnValue(strColCombIdent As String, strColumnName As String) As String
    On Error Resume Next

       Dim strRetResult As String

        Dim wsMapMasterRefSheet As Worksheet
        'Referes to the table Table2.
        Dim loMapMaster As ListObject
        Set wsMapMasterRefSheet = ThisWorkbook.Worksheets("Sheet3")
        Set loMapMaster = wsMapMasterRefSheet.ListObjects("Table2")

        'All rows of the table Table2
        Dim rAllRows As Range
        Set rAllRows = loMapMaster.DataBodyRange

        'Holds one row from the databody range for processing.
        Dim rCurrRow As Range

        'Process data
        Dim strTemp As String

        For Each rCurrRow In rAllRows

            strTemp = rCurrRow.Columns(2)

            Debug.Print strTemp
        Next rCurrRow

    findColumnValue = strRetResult
End Function

I was hoping to get results like below (ONLY the value of the column 2):

1.5
1.5
1.8
4
3
3
1
2

10
12
5
7

Instead I end up with something like this (All values from column#2 onwards, for each processing row.)

1.5
0.045150462962963

1.5
4.52083333333333E-02

1.8
4.72685185185185E-02

4
0.168090277777778

3
3.1

3
8.47800925925926E-02

1
4.16666666666667E-02

2
8.33449074074074E-02




10
10.1.1.1

12
1.3.4.5

5
0.212511574074074

7
8.54166666666667E-02

Using

strTemp = rCurrRow.Columns(1, 2)

instead of

strTemp = rCurrRow.Columns(2)

Causes runtime error 1004

Since each iteration points to a range object in the For loop; I was thinking using

rCurrRow.Columns(2)

will point to current Row's column#2 and hence print out only the column's value. Is my logic misplaced?

One additional question:

Why does the MSDN Excel Reference guide describes Columns as a Property; where as clearly the "Columns" usage clearly takes parameters

Here is the link I referred: http://msdn.microsoft.com/en-us/library/office/ff197454(v=office.15).aspx

Answer 1

Either specify you want to iterate rows:

    For Each rCurrRow In rAllRows.Rows

or only look at the ListRows in the first place:

Function findColumnValue(strColCombIdent As String, strColumnName As String) As String
    On Error Resume Next

       Dim strRetResult As String

        Dim wsMapMasterRefSheet As Worksheet
        'Referes to the table Table2.
        Dim loMapMaster As ListObject
        Set wsMapMasterRefSheet = ThisWorkbook.Worksheets("Sheet3")
        Set loMapMaster = wsMapMasterRefSheet.ListObjects("Table2")

        'All rows of the table Table2
        Dim rAllRows As ListRows
        Set rAllRows = loMapMaster.ListRows

        'Holds one row from the databody range for processing.
        Dim rCurrRow As ListRow

        'Process data
        Dim strTemp As String

        For Each rCurrRow In rAllRows

            strTemp = rCurrRow.Range(, 2)

            Debug.Print strTemp
        Next rCurrRow

    findColumnValue = strRetResult
End Function

Answer 2

You can call your variable rCurrRow all you want; VBA still won't know that you mean for it to contain an entire row of range rAllRows. It just assumes that rCurrRow represents one cell, such that For Each rCurrRow In rAllRows means "for each individual cell in this range".

What you need to do is limit the range being looped through. This should work; not tested.

    For Each rCurrRow In rAllRows.Columns(2)
        strTemp = rCurrRow
        Debug.Print strTemp
    Next rCurrRow

In fact I wouldn't call that variable rCurrRow at all; if you're going to use it in this way, call it e.g. cell instead.

EDIT: now that you have clarified your question in a comment below, you could do this:

    For i = 1 To rAllRows.Rows.Count
        Set rCurrRow = rAllRows.Rows(i)
        strTemp = rCurrRow.Cells(1,2)
        Debug.Print strTemp
    Next i

But even better and faster would be to load the entire range to a two-dimensional Variant array at once, and loop over that array — much faster than looping over many cells.

    Dim v As Variant
    v = rAllRows ' load entire range to a 2D array
    For i = 1 To UBound(v,1)
        strTemp = v(i,2)
        Debug.Print strTemp
    Next i        

Why does the MSDN Excel Reference guide describes Columns as a Property; where as clearly the "Columns" usage clearly takes parameters

Both methods and properties can take parameters. The distinction is more or less as follows:

• Properties are things that you can get (like a range's Address, which takes no parameter, or subrange such as Column or Row or Cells, which do) and/or set (like a range's .Interior.Color, or .Hidden status). They are usually nouns.

• Methods are things that do something to/with the range, and as such are usually verbs. Like .Select (takes no parameters) or .Copy (takes one parameter) or even .Speak.