Loop through column headers to search from column name and get cell range

[Pie]

Hello,

I've been searching all day to try to find an answer to my question.
Unfortuneately I haven't been able to find anything specific to my
issu. This may be because I am not using the right keywords, so
forgive me if this is a duplicate question of another posting.

Anyway, I have an excel spreadsheet of data that is sent to my group
each month. I am trying to automate the process of extracting the
data/columns that we need to import into access. 2 of the columns
contain fullnames. I need to split these columns into LastName and
FirstName which I have already done. However, in my code it the column
is chosen by the column range i.e (Q:Q). However, this is not always
true every month, so I wanted to be able to pick the column by the
column header instead. I am using the code below, but for some reason
it isn't moving across the column. It is moving down the rows. Can
someone please take a look at it and tell me what I am missing?

Thanks

SearchValue = "GOwner" ' Set search value"

Set rng =
Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
'lLastRow = rng.Row
lLastCol = rng.End(xlToRight).Column

Dim cVal As String
For i = 1 To lLastCol
MsgBox "Column = " & i
If Cells(i, "A").Value = SearchValue Then
MsgBox "Search value found at Column: " & i
End If
Next i

 

[Tom Ogilvy]

Dim col as Long, res as Variant
Dim SearchValue as String
SearchValue = "GOwner"
res = application.Match(SearchValue,rows(1),0)
if iserror(res) then
msgbox "GOwner not found"
exit sub
End if
col = res

but for your question:

For i = 1 To lLastCol
MsgBox "Column = " & i
If Cells(1,i).Value = SearchValue Then
MsgBox "Search value found at Column: " & i
End If
Next i

 

[Blue Hornet]

Hello,

I've been searching all day to try to find an answer to my question.
Unfortuneately I haven't been able to find anything specific to my
issu. This may be because I am not using the right keywords, so
forgive me if this is a duplicate question of another posting.

Anyway, I have an excel spreadsheet of data that is sent to my group
each month. I am trying to automate the process of extracting the
data/columns that we need to import into access. 2 of the columns
contain fullnames. I need to split these columns into LastName and
FirstName which I have already done. However, in my code it the column
is chosen by the column range i.e (Q:Q). However, this is not always
true every month, so I wanted to be able to pick the column by the
column header instead. I am using the code below, but for some reason
it isn't moving across the column. It is moving down the rows. Can
someone please take a look at it and tell me what I am missing?

Thanks

SearchValue = "GOwner" ' Set search value"

Set rng =
Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
'lLastRow = rng.Row
lLastCol = rng.End(xlToRight).Column

Dim cVal As String
For i = 1 To lLastCol
MsgBox "Column = " & i
If Cells(i, "A").Value = SearchValue Then
MsgBox "Search value found at Column: " & i
End If
Next i

If you want to search for values in, say, Row 1, then the code you want
in your For / Next loop is:
Cells( 1, i).Value
The syntax is Cells( Rowindex, Columnindex)

Chris

 

[Pie]

Thank you, Thank you. I knew it was something really small that I was
doing wrong.

 

[Pie]

Thanks Chris

I see where I went wrong. errr!

 

[Pie]

Thank you, Thank you, Tom I knew it was something really small that I
was doing wrong.

 

[Pie]

Just realized something
The variable i gives me my column location, but it gives me the number
of the column. I need the letter i.e Column(Q:Q).Select. How caan I
transpose the number to the corresponding column letter or is there a
better way to do this/

Thanks

 

[Tom Ogilvy]

I have been programming for years and have never need the letter. You can
always refer to it with the column number.

But if you want the letter and it will always be in column 26 or smaller

colLtr = chr(i+64)

if it could be into the double letters

colltr = left(cells(1,i).Address(0,0), 2 + (i <= 26))

demo'd from the immediate window:

i = 26
? left(cells(1,i).Address(0,0), 2 + (i <= 26))
Z
i = 32
? left(cells(1,i).Address(0,0), 2 + (i <= 26))
AF

 

[Pie]

If I don't need the letter thats fine, but I keep getting an error with
the number:

For i = 1 To lLastCol
If Cells(1, i).Value = SearchValue Then
cVal = i
End If
Next i

Columns(cVal).Select

 

[Tom Ogilvy]

i = 10
? columns(i).Address
$J:$J
i = "J"
? columns(i).Address
$J:$J
? columns("J").Address
$J:$J
? columns(10).address
$J:$J

All work for me as you can see from this demo from the immediate window. .

? columns("10").Address

is problematic