Get Column Header Value
 

Hi Guys
Just looking for some help. Haven't written any vba for quite some years
now. I have a spreadsheet with column headers along the top and column one
being a description column. What I need to do is after a description is
entered in rows under column A and for each row a $ value is entered under
one of the column headings anywhere from a -z (lets say) get the column
heading name for the activecell.value I then plan on using this value in a
variable to use a lookup function (I will reference the column headings on
another sheet and prob a Y/N field in the 2nd column on that sheet .. I
should make it a dynamic name range in case new columns get added *note to
self) to determine if this column is one of the columns I am interested in
(in which case I will have the code do something eg.. add gst to column AAA
on the main page say or ignore). I want to make the code dynamic so I would
like to have it acivate on every $ entry made (so maybe as a worksheet change
event). Any suggestions to a better approach or any code offerings would be
greatly appreciated.
I am unable to touch any column name values between A - AZ as these are used
and have basic code which uses offset values referring to these columns (to
change this would be too big of a task). Hoping to see some old names still
floating around, Tom, Stephen, John, Chip etc..

Thank you in advance

Get Column Header Value
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zerowaycool" wrote in message
...
Hi Guys
Just looking for some help. Haven't written any vba for quite some years
now. I have a spreadsheet with column headers along the top and column one
being a description column. What I need to do is after a description is
entered in rows under column A and for each row a $ value is entered under
one of the column headings anywhere from a -z (lets say) get the column
heading name for the activecell.value I then plan on using this value in
a
variable to use a lookup function (I will reference the column headings on
another sheet and prob a Y/N field in the 2nd column on that sheet .. I
should make it a dynamic name range in case new columns get added *note to
self) to determine if this column is one of the columns I am interested
in
(in which case I will have the code do something eg.. add gst to column
AAA
on the main page say or ignore). I want to make the code dynamic so I
would
like to have it acivate on every $ entry made (so maybe as a worksheet
change
event). Any suggestions to a better approach or any code offerings would
be
greatly appreciated.
I am unable to touch any column name values between A - AZ as these are
used
and have basic code which uses offset values referring to these columns
(to
change this would be too big of a task). Hoping to see some old names
still
floating around, Tom, Stephen, John, Chip etc..

Thank you in advance

Get Column Header Value
 

I would think you'd want to tie into the worksheet_Change event (not use the
activecell).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim HeaderStr As Variant 'string, number, ???
Dim res As Variant
Dim LookUpRng As Range

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("B2:z30")) Is Nothing Then
Exit Sub
End If

HeaderStr = Me.Cells(1, Target.Column).Value

Set LookUpRng = Worksheets("sheet1").Range("A:B")

res = Application.VLookup(HeaderStr, LookUpRng, 2, 0)

If IsError(res) Then
MsgBox "No match, the board goes back!"
Else
MsgBox res 'what should happen here?
End If

End Sub


Zerowaycool wrote:

Hi Guys
Just looking for some help. Haven't written any vba for quite some years
now. I have a spreadsheet with column headers along the top and column one
being a description column. What I need to do is after a description is
entered in rows under column A and for each row a $ value is entered under
one of the column headings anywhere from a -z (lets say) get the column
heading name for the activecell.value I then plan on using this value in a
variable to use a lookup function (I will reference the column headings on
another sheet and prob a Y/N field in the 2nd column on that sheet .. I
should make it a dynamic name range in case new columns get added *note to
self) to determine if this column is one of the columns I am interested in
(in which case I will have the code do something eg.. add gst to column AAA
on the main page say or ignore). I want to make the code dynamic so I would
like to have it acivate on every $ entry made (so maybe as a worksheet change
event). Any suggestions to a better approach or any code offerings would be
greatly appreciated.
I am unable to touch any column name values between A - AZ as these are used
and have basic code which uses offset values referring to these columns (to
change this would be too big of a task). Hoping to see some old names still
floating around, Tom, Stephen, John, Chip etc..

Thank you in advance

--

Dave Peterson