VBA Column name as column Hearder

[justvba]

I am trying to see if I can use the column header name "What Fruit" instead of like "E"?

Here is a part of my code that I am changing about 15 times but I want to be able to add columns to my sheet with out Messing up my VBA code.



Range("d5").Select

With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

Do Until ActiveCell.Row > LastRow
FRUIT= UCase(Trim(Trim(Range("E" & ActiveCell.Row))))
sqlstatement = " " & _
" SELECT Apple FROM TableName" & _
"WHERE ITEM IN ('" & FRUIT & "')"


Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:=" " & sqlstatement & " ", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenForwardOnly
On Error Resume Next
SomeVarible = rstRecordset.Fields("Apple").Value
If SomeVarible = 0 Then
GoTo NextItem6
Else
End If
ActiveCell.CopyFromRecordset rstRecordset
NextItem:
ActiveCell.Offset(1, 0).Select
Loop

 

[Norie]

You could use Application.Match to find the column number for the column with the header 'What Fruit'.

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

        ' assumes headers in row 1
        Res = Application.Match("What Fruit", .Rows(1), 0)
    End With

    Do Until ActiveCell.Row > LastRow
        FRUIT = UCase(Trim(Cells(ActiveCell.Row, Res)))

        sqlstatement = " SELECT Apple " & _
                       " FROM TableName " & _
                       "WHERE ITEM IN ('" & FRUIT & "')"


        ' rest of code
    End Sub

 

[justvba]

You could use Application.Match to find the column number for the column with the header 'What Fruit'.

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

        ' assumes headers in row 1
        Res = Application.Match("What Fruit", .Rows(1), 0)
    End With

    Do Until ActiveCell.Row > LastRow
        FRUIT = UCase(Trim(Cells(ActiveCell.Row, Res)))

        sqlstatement = " SELECT Apple " & _
                       " FROM TableName " & _
                       "WHERE ITEM IN ('" & FRUIT & "')"


        ' rest of code
    End Sub

TOk but how do I use the Res to select a certain cell?

Range(D5).select
would ir be

Range (Res,5).select
???

 

[Norie]

Which cell do you actually want to select?

Is it a cell in column D or a cell in the column with the header 'What Fruit'?

Which row should the selected cell be in?

 

[justvba]

I want to to be column with header 'What Fruit' and row 5

 

[justvba]

Also How can I use Column header 'What Fruit' to put in a formula I need to do a Vlookup.
the Current Formula is as follows;
"=iferror(VLOOKUP(trim(Upper(RC[-22])),Sheet2!C[-26]:C[-25],2,0),"""")"

I need to make RC[-22] to be 'What Fruit'

 

[justvba]

Also How can I use Column header 'What Fruit' to put in a formula I need to do a Vlookup.
the Current Formula is as follows;
"=iferror(VLOOKUP(trim(Upper(RC[-22])),Sheet2!C[-26]:C[-25],2,0),"""")"

I need to make RC[-22] to be 'What Fruit'

Also how would you do this with 2 column names for example
M2 would be 'Price'
N2 would be 'QtySold'

=M2*N2