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

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
(E-Mail Removed)
"Gilgamesh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

"B Lynn B" <(E-Mail Removed)> wrote in message
news:C927CA84-B190-4E48-9F3A-(E-Mail Removed)...
> 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
>>
>> .
>>

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" <(E-Mail Removed)> wrote in message

"Gilgamesh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>