Refering to Colums by Name

[Tiiatcha]

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.

    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

 

[PCL]

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

Mycol = Range('"Myheader1").column

 

[Peter_SSs]

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
	A	B	C	D	E	F
1	2	1st col of interest			2nd col of interest	3rd col of interest
2	5
3	6
4		Actual sheet starts here
5
Refer to Columns

Then, in your code, you can proceed like this:

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.

 

[Tiiatcha]

That should work a treat, will let you know

Thank you Peter SS