About Us

Charlotte E

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,

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to return a column number?	sycsummit	Excel Programming	3	July 24th 08 11:35 PM
Return column number from column header text	Roger[_3_]	Excel Discussion (Misc queries)	4	February 14th 08 09:40 PM
How to return column number	moonhk	Excel Programming	4	November 7th 07 04:10 PM
To return the column number of a string	[email protected]	Excel Worksheet Functions	1	June 28th 07 07:28 PM
Excel - Return column alphabet from column number	Sathyaish	Excel Programming	2	April 7th 04 02:40 PM

Menu

About Us