Pick a cell in a named column

[RaunLGoode]

I have a worksheet and column "C" is "Sales". Because people may add/delete additional columns or otherwise move this column around in the worksheet I want to define the "Sales" column as a range. Hopefully I won't have to adjust the code if the column position changes.
Once I do this how would I reference a specific cell using the Range name as a column designation? ( ie insted od [Range("C21".Select)] I would use[Range("Sales" & 21).Select] This returns an error, What would the proper syntax be?
I must be tired, because I should know this

 

[chergh]

Right idea but you can't used a named range in the way you are attempting to.

Even though you have assinged a range name to the column that range doesn't return a column letter the way you want.

There are two ways to approach this.

The first is depending upon how comfortable you would be mixing traditional range addresses like "A1" or "C10" with r1c1 notation, i.e. in r1c1 notation A1 would be cell 1,1 and C10 would 10,3 (the row and columns are reversed in r1c1.

If you are comfortable to mix it up like this then you would write something like:

cells(21,range("sales").Column).select

The other option is to write functions to return the column letter when you pass the column number. Here's two I made earlier:

Public Function RetColLett(colNum As Long) As String

If colNum < 27 Then
    RetColLett = Left(ThisWorkbook.Worksheets(1).Cells(1, colNum).Address(rowabsolute:=False, columnabsolute:=False), 1)
Else
    RetColLett = Left(ThisWorkbook.Worksheets(1).Cells(1, colNum).Address(rowabsolute:=False, columnabsolute:=False), 2)
End If

End Function

Public Function RetColNum(collet As String) As Long

   RetColNum = ThisWorkbook.Worksheets(1).Range(collet & "1").Column

End Function

You will be mainly interested in the first function which returns the letter from a column number.

So to do what you want using the above functions it would be:

range(RetColLett(range("sales").column) & 10).select

 

[RaunLGoode]

Thank you for your help and prompt reply