|
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
Charlie,
That will error if the CalAddress has more than one cell... HTH, Bernie MS Excel MVP simpler ColumnNumber = Range(ColAddress).Column |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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) |
Return Column Number
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, |
Return Column Number
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, |
Return Column Number
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, |
| All times are GMT +1. The time now is 07:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com