column adjustments

[dstiefe]

I am writing the VBA code that is telling me what column a certain piece of
data is in...but instead of giving me the number of the column...is there
someway through VBA that it i can get the letter reference.

 

[Rick Rothstein]

The code you would use depends on what you are doing to locate the column.
For example, if you have the column number, you could do this...

ColNum = 4
ColLetter = Split(Columns(ColNum).Address(0,0),":")(0)

If you have a cell reference that is in that column, then you could do
this...

ColLetter = Split(CellReference.Address(1, 0), "$")(0)

There are lots of possibilities depending on what you are doing... can you
show us the code you have so we can give you a specific answer.

 

[JLGWhiz]

Here is a Function, courtesy Chip Pearson.

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function


To use the function in code, assume you have set a column number to variable
myCol then:

myColLtr = ColumnLetter(myCol)

myColLtr will now equal an alpha column designation.