Refering to Colums by Name

Tiiatcha

New Member
Joined
Dec 14, 2008
Messages
8
Hi I am looking for a way to refer to a column by name instead of letter/number.

I know I can name ranges including entire colums but this does not seem to fit into my code.

Here is a sample of what I have been doing, what I want to do is replace the column header such as "C" with a name. The idea being is that when the end user goes and inserts a column they still get the correct results from the VBA procedures.

Code:
    If Not Intersect(Target, Me.Range(WS_RANGE & fR)) Is Nothing Then
 
    With Target
 
    If Me.Cells(.Row, "B").Value <> "" Then
        Me.Cells(.Row, "A").Value = Int(Me.Cells(.Row - 1, "A").Value) + 1
        Me.Cells(.Row, "C").Value = Format(Me.Cells(.Row, "B").Value, "DDDD")
    End If
    End With
    End If
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
A possibility could be to use a header and why not give the same name as the header:
C1 = Myheader1 with name = Myheader1
Then in the macro you can use the column

Code:
Mycol = Range('"Myheader1").column
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Another work-around would be to set up column A as shown below and hide columnA and rows 1:3. So your visible sheet actually starts at cell B4. The entries in B1, E1 and F1 are just to indicate to you the columns of interest in your code.

Excel Workbook
ABCDEF
121st col of interest2nd col of interest3rd col of interest
25
36
4Actual sheet starts here
5
Refer to Columns





Then, in your code, you can proceed like this:
Rich (BB code):
Dim c1 As Long, c2 As Long, c3 As Long

c1 = Range("A1").Value
c2 = Range("A2").Value
c3 = Range("A3").Value

With Target
    If Me.Cells(.Row, c2).Value <> "" Then
        Me.Cells(.Row, c1).Value = Int(Me.Cells(.Row - 1, c1).Value) + 1

If columns are inserted/deleted (provided column A or a column of interest is not deleted), the formulas in column A will update and the new column numbers will be available next time the code is run.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top