How to reference column by name

  • Thread starter Thread starter Scott Steiner
  • Start date Start date
S

Scott Steiner

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!
 
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)
 
Bob said:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("F:F")) Is Nothing Then
...
End If
End Sub
Click to expand...

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
Click to expand...

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.
 
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 said:
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!


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
Click to expand...
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 said:
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!


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
Click to expand...
name.
 
Back
Top