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,

Always post your code for comments and suggestions. ALWAYS!!


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Charlotte E" <@ wrote in message
...
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,

I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function

--
HTH...

Jim Thomlinson


"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,

Wauw!

That one was just about 7 times as effective as mine: 5 lines to 36 lines
:-)

Thanks :-)))



Jim Thomlinson wrote:
I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function


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,

Always post your code for comments and suggestions. ALWAYS!!


??? Why ?!?

As stated, I knew my code was not effective, so no need for posting it for
comments and telling me that...

All I asked for was hints to help me in the right direction - and Jim didn't
need to see my code to actually give me the full solution!

Now, that's what I call helping a fellow Excel nerd :-)

Thanks to Jim :-)))




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,

simpler

ColumnNumber = Range(ColAddress).Column


"Jim Thomlinson" wrote:

I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function

--
HTH...

Jim Thomlinson


"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,

It can actually be much shorter:

Public Function ColumnNumber(ByVal ColAddress As Variant) As Integer
ColumnNumber = Range(ColAddress).Cells(1, 1).Column
End Function

HTH,
Bernie
MS Excel MVP


"Charlotte E" <@ wrote in message ...
Wauw!

That one was just about 7 times as effective as mine: 5 lines to 36 lines :-)

Thanks :-)))



Jim Thomlinson wrote:
I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function


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,

nevermind, I see what I missed before clicking "Post"

"Charlie" wrote:

simpler

ColumnNumber = Range(ColAddress).Column


"Jim Thomlinson" wrote:

I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function

--
HTH...

Jim Thomlinson


"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,

I don't think Don was trying to critique your code. It's just that sometimes
people here can give you more ideas for future use! I have tons of notes
saved from years of visiting here.

"Charlotte E" wrote:


Always post your code for comments and suggestions. ALWAYS!!


??? Why ?!?

As stated, I knew my code was not effective, so no need for posting it for
comments and telling me that...

All I asked for was hints to help me in the right direction - and Jim didn't
need to see my code to actually give me the full solution!

Now, that's what I call helping a fellow Excel nerd :-)

Thanks to Jim :-)))




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,

Charlie,

That will error if the CalAddress has more than one cell...

HTH,
Bernie
MS Excel MVP

simpler

ColumnNumber = Range(ColAddress).Column

And what happens to "C" and 3 when you do that since those are not valid
range addresses?
--
HTH...

Jim Thomlinson


"Charlie" wrote:

simpler

ColumnNumber = Range(ColAddress).Column


"Jim Thomlinson" wrote:

I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function

--
HTH...

Jim Thomlinson


"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,

What Happens with "C" and 3 which are not valid range addresses? Mine goes
boom when I try that...
--
HTH...

Jim Thomlinson


"Bernie Deitrick" wrote:

It can actually be much shorter:

Public Function ColumnNumber(ByVal ColAddress As Variant) As Integer
ColumnNumber = Range(ColAddress).Cells(1, 1).Column
End Function

HTH,
Bernie
MS Excel MVP


"Charlotte E" <@ wrote in message ...
Wauw!

That one was just about 7 times as effective as mine: 5 lines to 36 lines :-)

Thanks :-)))



Jim Thomlinson wrote:
I would probably do something like this...

Public Function ColumnNumber(ByVal ColAddress As Variant) As Long
Dim rng As Range

On Error Resume Next
Set rng = Range(ColAddress)
Set rng = Columns(ColAddress)
ColumnNumber = rng(1).Column
End Function


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,

Always post your code for comments and suggestions. ALWAYS!!

??? Why ?!?

Because it allows us to see how you were trying to approach the problem and,
if possible, allows us to make comments and suggestions to help guide you in
your future programming endeavors. As things stand now, you have a solution
to your problem, but you don't know how efficient, usable or applicable your
approach to coding might be for future problems that you might try to write
code for. Now, it may be possible that we have no useful suggestions on your
coding or its style; but then again, we might be able to offer you some
insights that you might find useful. Exactly what did you have to lose by
copy/pasting your attempted code solution instead of your somewhat snippy
(my interpretation) "???Why?!?" response?

--
Rick (MVP - Excel)

Your call, of course. Jim is younger and more perceptive than I. <G
See Rick's post
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Charlotte E" <@ wrote in message
...

Always post your code for comments and suggestions. ALWAYS!!


??? Why ?!?

As stated, I knew my code was not effective, so no need for posting it for
comments and telling me that...

All I asked for was hints to help me in the right direction - and Jim
didn't need to see my code to actually give me the full solution!

Now, that's what I call helping a fellow Excel nerd :-)

Thanks to Jim :-)))




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,

I agree. I've been using Excel since the very first version (198?) and today
I learnt that SPLIT could return one item !!! wow. We all can learn!

"Charlie" wrote in message
...
I don't think Don was trying to critique your code. It's just that
sometimes
people here can give you more ideas for future use! I have tons of notes
saved from years of visiting here.

"Charlotte E" wrote:


Always post your code for comments and suggestions. ALWAYS!!


??? Why ?!?

As stated, I knew my code was not effective, so no need for posting it
for
comments and telling me that...

All I asked for was hints to help me in the right direction - and Jim
didn't
need to see my code to actually give me the full solution!

Now, that's what I call helping a fellow Excel nerd :-)

Thanks to Jim :-)))




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,

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,

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,