Get Column Header Value

  • Thread starter Thread starter Zerowaycool
  • Start date Start date
Z

Zerowaycool

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
 
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.
 
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
 
Back
Top