Finction to find the first cell that is not zero

[Mark]

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

 

[Ron Rosenfeld]

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

 

[Stan Brown]

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.

 

[Mark]

Hi Ron

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

Cheers...

 

[Ron Rosenfeld]

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