Trying to select a cell through VBA - skipping to several columns over

[CWMacNut]

Using the code below, I am trying to select the next cell down from the header so I can select just the data in the column without the header.

        For i = 1 To HeaderCount
                If HeaderNames(i) = "ZZZ" Then
'                        MsgBox ("Column # is: " & i)  ' [B][COLOR=#008000]<===== answers correctly[/COLOR][/B]
                        Cells(i & ",2").Select  ' [COLOR=#008000][B]<===== Selects cell (L1)!!!  i does not equal 12!![/B][/COLOR]
                        Range(Selection, Selection.End(xlDown)).Select
'                        Selection.Copy
                        Exit For
                End If
        Next

What on earth am I doing wrong??

Never mind! I am so sorry but I found the answer (Offset property). I will work on not being so quick to post -- do research BEFORE posting a question!!

Thank you,
CWMacNut
Graphics Engineer

 

[steve the fish]

Cells wants row number and column number like this for example:

Cells(1,2)

which is row 1, column 2 or cell B1.

 

[Fluff]

Try

   For i = 1 To HeaderCount
      If HeaderNames(i) = "ZZZ" Then
         Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Copy
         Exit For
      End If
   Next i

 

[steve the fish]

Think id go like this for your problem mind:

With Sheets("Sheet1")
    Set Rng = .Rows(1).Find(What:="XXX", LookIn:=xlValues, LookAt:=xlWhole)
    If Not Rng Is Nothing Then
        lr = .Cells(.Rows.Count, Rng.Column).End(xlUp).Row
        .Range(.Cells(2, Rng.Column), .Cells(lr, Rng.Column)).Copy
    Else
        MsgBox "Cant find the header"
    End If
End With