Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column name denoting initial..???
hi,
i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column name de
hi
here's a formula, not the best one, but it works: =IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(A1+64)) A1 is the input field, where the user can enter the column-number! hth If anyone has a better solution, please post it, i'm really interested in it. Cheers Carlo " wrote: hi, i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column nam
Just saw, that you want the other way around :)
it's even easier that way ;) =IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input") hth Carlo "Carlo" wrote: hi here's a formula, not the best one, but it works: =IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(A1+64)) A1 is the input field, where the user can enter the column-number! hth If anyone has a better solution, please post it, i'm really interested in it. Cheers Carlo " wrote: hi, i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column nam
Carlo wrote: Just saw, that you want the other way around :) it's even easier that way ;) =IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input") hth Carlo "Carlo" wrote: hi here's a formula, not the best one, but it works: =IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(A1+64)) A1 is the input field, where the user can enter the column-number! hth If anyone has a better solution, please post it, i'm really interested in it. Cheers Carlo " wrote: hi, i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. hey i dont know how to implement the formula you have given. just answer me like this function col_number(Col_name) col_name = "AB" col_no = ???????? msgbox col_no end function |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column nam
Hi Abu,
as you are in the Worksheet function forum, i assumed you need a formula for a cell, heres the code: '---------------------------------- Function col_number(col_name) If Len(col_name) = 1 Then col_no = Asc(col_name) - 64 ElseIf Len(col_name) = 2 Then col_no = (Asc(Left(col_name, 1)) - 64) * 26 + Asc(Right(col_name, 1)) - 64 Else col_no = "Wrong Input" End If MsgBox col_no End Function '---------------------------------- Please choose the right forum hth Cheers Carlo " wrote: Carlo wrote: Just saw, that you want the other way around :) it's even easier that way ;) =IF(LEN(A7)=1;CODE(A7)-64;IF(LEN(A7)=2;(CODE(LEFT(A7;1))-64)*26+CODE(RIGHT(A7;1))-64);"wrong input") hth Carlo "Carlo" wrote: hi here's a formula, not the best one, but it works: =IF(A126;CHAR((A1-IF(MOD(A1;26)=0;26;MOD(A1;26)))/26+64)&CHAR(IF(MOD(A1;26)=0;26;MOD(A1;26))+64);CHA R(A1+64)) A1 is the input field, where the user can enter the column-number! hth If anyone has a better solution, please post it, i'm really interested in it. Cheers Carlo " wrote: hi, i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. hey i dont know how to implement the formula you have given. just answer me like this function col_number(Col_name) col_name = "AB" col_no = ???????? msgbox col_no end function |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the column number from the corresponding column name denoting initial..???
Abu
Couple of UDF's. Function GetColNum(myColumn As String) As Integer GetColNum = Columns(myColumn & ":" & myColumn).Column End Function usage.........=getcolnum("a") returns 1 Function GetColLet(ColNumber As Integer) As String GetColLet = Left(Cells(1, ColNumber).Address(False, False), _ 1 - (ColNumber 26)) End Function usage........=getcollet(27) returns AA Gord Dibben MS Excel MVP On 10 Sep 2006 22:39:22 -0700, wrote: hi, i want to recieve the column number ( ranging from 1 to 256 ) while the input will be the column name (ranging from A to IV ). please provide me a macro solution to reach the above mentioned requirement. Thanks and Regards Abu. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Multiply two columns in Excel or one column by one number | New Users to Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |