Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM". The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value "AM"
' Alternatively can be called as =col() which returns the column of the
current cell


Select Case Column

Case Is 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function

Function colid() As String
v = Application.Caller.Address
colid = Split(v, "$")(1)
End Function

This gives the column ID of the cell containing the formula. (part of your
requirement)
--
Gary''s Student - gsnu200778


"dhstein" wrote:

Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM". The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value "AM"
' Alternatively can be called as =col() which returns the column of the
current cell


Select Case Column

Case Is 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function

Can you make use of this worksheet formula instead?

=SUBSTITUTE(ADDRESS(1,COLUMN(AM2),4),"1","")

You would put in the cell reference in the COLUMN function, or leave it
empty, and it will give you that cell's column ID in letters.

Rick


"dhstein" wrote in message
...
Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM".
The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong
Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value
"AM"
' Alternatively can be called as =col() which returns the column of the
current cell


Select Case Column

Case Is 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function

I don't know what you're using in the =indirect(), but that has a parm that
allows you to use R1C1 reference style, too.



dhstein wrote:

Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM". The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value "AM"
' Alternatively can be called as =col() which returns the column of the
current cell

Select Case Column

Case Is 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function

--

Dave Peterson

Thanks Gary Rick and Dave. By replacing Addr = Selection.Address with
Addr = Application.Caller.Address it works fine. (thanks Gary). Gary -
your code is much more efficient than mine for the Case = 0 option. Dave -
thanks for the info on the INDIRECT function. I sometimes build an address
by concatenating a row and column e.g A1 might equal "BC" and A2 might
equal 14 - so I use =INDIRECT(A1&A2) which gives me the value in cell BC14.
I'm not sure if the other method of INDIRECT will help with that, which is
why I need the function to return "BC" instead of 55.

if A1 contains BC and A2 contains 14, then =indirect(a1&a2) will return what's
in BC14

If A1 contains 55 and a2 contains 14, then =indirect("r"&a2&"c"&a1,false) will
return what's in BC14. There is no need to convert 55 to BC.

It's like writing:
=R14C55
when you're using R1C1 reference style.



dhstein wrote:

Thanks Gary Rick and Dave. By replacing Addr = Selection.Address with
Addr = Application.Caller.Address it works fine. (thanks Gary). Gary -
your code is much more efficient than mine for the Case = 0 option. Dave -
thanks for the info on the INDIRECT function. I sometimes build an address
by concatenating a row and column e.g A1 might equal "BC" and A2 might
equal 14 - so I use =INDIRECT(A1&A2) which gives me the value in cell BC14.
I'm not sure if the other method of INDIRECT will help with that, which is
why I need the function to return "BC" instead of 55.

--

Dave Peterson

You are very welcome!
--
Gary''s Student - gsnu200778


"dhstein" wrote:

Thanks Gary Rick and Dave. By replacing Addr = Selection.Address with
Addr = Application.Caller.Address it works fine. (thanks Gary). Gary -
your code is much more efficient than mine for the Case = 0 option. Dave -
thanks for the info on the INDIRECT function. I sometimes build an address
by concatenating a row and column e.g A1 might equal "BC" and A2 might
equal 14 - so I use =INDIRECT(A1&A2) which gives me the value in cell BC14.
I'm not sure if the other method of INDIRECT will help with that, which is
why I need the function to return "BC" instead of 55.

Thanks - I'd been puzzled about how to find the cell that called a macro. I
had reluctantly resorted to passing the calling cell as a parameter.

Application.caller is much better.

Ed

"Gary''s Student" wrote:

Function colid() As String
v = Application.Caller.Address
colid = Split(v, "$")(1)
End Function

This gives the column ID of the cell containing the formula. (part of your
requirement)
--
Gary''s Student - gsnu200778


"dhstein" wrote:

Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM". The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value "AM"
' Alternatively can be called as =col() which returns the column of the
current cell


Select Case Column

Case Is 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function