Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Loop through the first column of a table range in Excel using VBA

This should be pretty straightforward but I'm a little stuck.

I have a table called "ClientReturns" in a worksheet. The first column of this table contains the account number of a client.

I want to loop through the table getting the account number each time. This is what I have so far but it also goes through the data in the other columns when I only want the data in the first column.

Sub doStuff()

    Set ClientTable = Sheets("Returns").range("ClientReturns")
    For Each tRow In ClientTable
        AccNum = tRow.Columns(1).Value
        'Do stuff with the account num
    Next AccNum

End Sub

Many thanks for the answers... I solved it with this:

Sub getReturns()

    Dim lookR As range, c As range
    With Sheets("Returns").ListObjects("ClientReturns")
        Set lookR = .ListColumns("Account Number").DataBodyRange
        For Each AccNum In lookR.Cells
            'Let's see if it's a combined account
        Next AccNum
    End With

End Sub
like image 595
harryg Avatar asked Oct 05 '12 11:10

harryg


1 Answers

More directly just work with the first column

Dim ClientTable As Range
Dim rng1 As Range
Set ClientTable = Sheets("Returns").Range("ClientReturns")
For Each rng1 In ClientTable.Columns(1).Cells
    AccNum = rng1.Value
Next
like image 74
brettdj Avatar answered Nov 18 '22 21:11

brettdj