Prev: Updating another workbook
Next: A generic "find" function
From: Zerowaycool on 5 May 2010 08:37
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
From: Don Guillett on 5 May 2010 09:25
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
dguillett(a)gmail.com
"Zerowaycool" <Zerowaycool(a)discussions.microsoft.com> wrote in message
news:96E390DC-6B83-4F44-AE11-163646C032A3(a)microsoft.com...
> 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

From: Dave Peterson on 5 May 2010 09:30
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
 | 
Pages: 1
Prev: Updating another workbook
Next: A generic "find" function