Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
Hi,
I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
mycolumn=application.match(myvalue,range("a1:cz1") ,0)
or use vba FIND with if not mycol is nothing then msgbox mycol.column -- Don Guillett Microsoft MVP Excel SalesAid Software "Gilgamesh" wrote in message ... Hi, I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
cells.find can be used to return a row/column number or address or any number
of other properties the cell might have. myRow = cells.find(What:="Joe Smith").row myCol = cells.find(What:="Joe Smith").column myAddr = cells.find(What:="Joe Smith").address "Gilgamesh" wrote: Hi, I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
Hello,
Enter formula in A2: =if(a1=value($A$5),$A$5,"") copy this formula from B2 to CZ1 by dragging. Enter a number in A5, say 12. You will see all your entries A1:CZ1 that contain a 12. Regards, Gabor Sebo "Gilgamesh" wrote in message ... Hi, I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
"B Lynn B" wrote in message ... cells.find can be used to return a row/column number or address or any number of other properties the cell might have. myRow = cells.find(What:="Joe Smith").row myCol = cells.find(What:="Joe Smith").column myAddr = cells.find(What:="Joe Smith").address Thank You "Gilgamesh" wrote: Hi, I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find a column number for a cell containing a value
Hello,
I wrote a VBA program and inserted a:12 in columns 1,6,11,30,92 and 104. The outputs are column numbers (1,6,...104) and the columns for these 12.s: A,F,K,AD,CN and CZ. The output is unsophisticated. best regards, Gabor Sebo worksheet encl. "Gilgamesh" wrote in message "Gilgamesh" wrote in message ... Hi, I have a range of cells A1:CZ1 all of which have different values. I need to find the column number for a cell that contains a certain value. I know I can use cells.find which returns the value rather than the cell reference, and I have seen other posts in this group to return a number when the cell is known range(ColAddress).cells(1,1).column I can easily put this in a simple loop but I was wondering if there was a smarter way that could do it in one line using built-in functions. If it was a loop it would have to be processed many times for the different values I need to lookup slowing things down quite a bit. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the last number of a column (lower cell) | Excel Discussion (Misc queries) | |||
Find column number of last used cell in a row | Excel Programming | |||
Find previous number and find next number in column | Excel Discussion (Misc queries) | |||
Find DMIN in a column range determined by a number in another cell | Excel Worksheet Functions | |||
Find cell below last number in a column | Excel Programming |