Thanks, chip - another step up the learning curce :-)


Chip Pearson wrote:
Another way, with a lot more flexibility than other replies:

Function ColNum(C As Variant) As Long
On Error GoTo ErrA:
If IsNumeric(C) Then
ColNum = Cells(1, CLng(C)).Column
Else
ColNum = Cells(1, C).Column
End If
Exit Function
ErrB:
On Error GoTo ErrC:
ColNum = Range(C, C)(1, 1).Column
Exit Function
ErrA:
Resume ErrB
ErrC:
End Function


It returns the column number of the first cell indicated by C. C can
be a number indicating a column; a string indicating a column number,
column letter, range specification, or defined name; or a Range
object. For example, all of the following will write a debug trace
value of 3:

Debug.Print ColNum(3)
Debug.Print ColNum("3")
Debug.Print ColNum("C")
Debug.Print ColNum("C:C")
Debug.Print ColNum("C1")
Debug.Print ColNum("C1:F10")
Debug.Print ColNum("TheCell")
Debug.Print ColNum(Range("C20"))

If C is not valid, the result is 0. E.g,

Debug.Print ColNum("asdf") ' returns 0


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 27 May 2009 18:34:03 +0200, "Charlotte E" <@ wrote:

I've made a (not so) small UDF to return the column number, no
matter how the input is given, ie.:


Function RCN(ColumnAddres as variant) as long

If doesn't matter, if I give en column as...

RCN(3)
RCN("C")
RCN("C7")
RCN("C:C")
RCN("$C$11")

...in all cases it returns the number 3, which is the column number
of the address given.

But, I kind of think my UDF is somewhat long and takes too many
lines for such a relative simple task.

Could anyone guide me to a simple solution, for a small UDF to solve
this little "problem"?


TIA,