How to reference column by name
 

Hi,

I want the following behaviour:

if the value of a cell changes, then I check if the column the cell is
in is a certain column, if yes then I do some calculation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
...
End If
End Sub

Question: How can I reference the queried column by name rather than by
number as above?

Thanks!

How to reference column by name
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("F:F")) Is Nothing Then
....
End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("columnF")) Is Nothing Then
....
End If
End Sub

where columnF is a range name

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scott Steiner" wrote in message
. ..
Hi,

I want the following behaviour:

if the value of a cell changes, then I check if the column the cell is
in is a certain column, if yes then I do some calculation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
...
End If
End Sub

Question: How can I reference the queried column by name rather than by
number as above?

Thanks!

How to reference column by name
 

Bob Phillips schrieb:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("F:F")) Is Nothing Then
...
End If
End Sub

This worked fine, but I need something else. I would like to reference
my column by "title" or "heading" i.e. what is written in row 1. Or
maybe there is even a way to define a unique name for each column
regardless of what is written in row 1, and then reference that column
by that unique name, that would be best!


or

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("columnF")) Is Nothing Then
...
End If
End Sub

where columnF is a range name

I tried replacing "columnF" by what is written in my column in row 1,
but that didn't work. So I guess what I replaced wasn't a valid range name.

How to reference column by name
 

I feared you might mean that <vbg.

Try this version

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Long
On Error Resume Next
iCol = Application.Match("column_label", Rows("1:1"), 0)
On Error GoTo 0
If iCol 0 Then
If Target.Column = iCol Then
....
End If
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scott Steiner" wrote in message
. ..
Bob Phillips schrieb:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("F:F")) Is Nothing Then
...
End If
End Sub

This worked fine, but I need something else. I would like to reference
my column by "title" or "heading" i.e. what is written in row 1. Or
maybe there is even a way to define a unique name for each column
regardless of what is written in row 1, and then reference that column
by that unique name, that would be best!


or

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("columnF")) Is Nothing Then
...
End If
End Sub

where columnF is a range name

I tried replacing "columnF" by what is written in my column in row 1,
but that didn't work. So I guess what I replaced wasn't a valid range
name.

How to reference column by name
 

Assume row of the target of interest contains the name/string ABCD

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.count 1 then exit sub
If Cells(1,Target.Column) = "ABCD" then


--
Regards,
Tom Ogilvy

"Scott Steiner" wrote in message
. ..
Bob Phillips schrieb:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("F:F")) Is Nothing Then
...
End If
End Sub

This worked fine, but I need something else. I would like to reference
my column by "title" or "heading" i.e. what is written in row 1. Or
maybe there is even a way to define a unique name for each column
regardless of what is written in row 1, and then reference that column
by that unique name, that would be best!


or

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("columnF")) Is Nothing Then
...
End If
End Sub

where columnF is a range name

I tried replacing "columnF" by what is written in my column in row 1,
but that didn't work. So I guess what I replaced wasn't a valid range
name.