Finction to find the first cell that is not zero
 

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is ther percahnce a function that will do this?

Mark

Finction to find the first cell that is not zero
 

On Sat, 9 Jun 2007 06:08:46 +1000, "Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is ther percahnce a function that will do this?

Mark



What do you mean by "find" the first cell?

This **array-entered** formula will return the column number of the first cell
in row 1 with a non-zero entry (enter with <ctrl-shift-enter)

=MATCH(1,ISNUMBER(1:1)*(1:1<0),0)

This **array-entered** formula will return the value in that cell:


=INDIRECT(ADDRESS(1,MATCH(1,ISNUMBER(1:1)*(1:1<0) ,0)))



--ron

Finction to find the first cell that is not zero
 

Sat, 9 Jun 2007 06:08:46 +1000 from Mark
:

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is ther percahnce a function that will do this?

Please don't post the same query multiple times.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Finction to find the first cell that is not zero
 

Hi Ron

Thanks for this, it has solved another issue that I have just stumbled
across with my data.

Cheers...

"Ron Rosenfeld" wrote in message
...
On Sat, 9 Jun 2007 06:08:46 +1000, "Mark"
wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is ther percahnce a function that will do this?

Mark



What do you mean by "find" the first cell?

This **array-entered** formula will return the column number of the first
cell
in row 1 with a non-zero entry (enter with <ctrl-shift-enter)

=MATCH(1,ISNUMBER(1:1)*(1:1<0),0)

This **array-entered** formula will return the value in that cell:


=INDIRECT(ADDRESS(1,MATCH(1,ISNUMBER(1:1)*(1:1<0) ,0)))



--ron

Finction to find the first cell that is not zero
 

On Tue, 12 Jun 2007 10:58:14 +1000, "Mark"
wrote:

Hi Ron

Thanks for this, it has solved another issue that I have just stumbled
across with my data.

Cheers...


You're welcome. Glad to help.
--ron