Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks

When you say you have a list of numbers starting with zero do you mean
0.nnnnnnn or are the cells formatted as text? if its the latter then
they are no longer numbers, if you were to use them in a calculation you
would lose the zero, can you confirm the format?

Lucile;538036 Wrote:
Hi,

I have a list of number starting with a bunch of 0 and I need to find
the
first cell with a number different from 0. And copy this value on an
other
sheet.

Any idea?
Thanks


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147876

Hi,

Try this. as long as there aonly numbers in the range

=INDEX(Sheet2!B1:B1000,MATCH(TRUE,Sheet2!B1:B1000 0,0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Lucile" wrote:

Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks

I forgot...
I need a piece of code for a VBA program.

Thanks

"Mike H" wrote:

Hi,

Try this. as long as there aonly numbers in the range

=INDEX(Sheet2!B1:B1000,MATCH(TRUE,Sheet2!B1:B1000 0,0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Lucile" wrote:

Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks

Are these numbers in a single column or single row? Or are they in an
arbitrary range? If in a column or row, does the list start at the first
cell in the column or row? Are there any empty cells in the range of cell?
Are these numbers whole numbers or can there be decimal values? Are you
looking for VB code or a worksheet formula?

--
Rick (MVP - Excel)


"Lucile" wrote in message
...
Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks

Try this

a=1
Do until Sheets("Sheet1").cells(A,1)<0
A=A+1
Loop
Sheets("Sheet2").cells(1,1)=Sheets("Sheet1").cells (A,1)

This assumes your data is starts in row 1, column 1 change as needed
--
If this helps, please remember to click yes.


"Lucile" wrote:

I forgot...
I need a piece of code for a VBA program.

Thanks

"Mike H" wrote:

Hi,

Try this. as long as there aonly numbers in the range

=INDEX(Sheet2!B1:B1000,MATCH(TRUE,Sheet2!B1:B1000 0,0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Lucile" wrote:

Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks

Thanks very much!

It seems to work!

"Paul C" wrote:

Try this

a=1
Do until Sheets("Sheet1").cells(A,1)<0
A=A+1
Loop
Sheets("Sheet2").cells(1,1)=Sheets("Sheet1").cells (A,1)

This assumes your data is starts in row 1, column 1 change as needed
--
If this helps, please remember to click yes.


"Lucile" wrote:

I forgot...
I need a piece of code for a VBA program.

Thanks

"Mike H" wrote:

Hi,

Try this. as long as there aonly numbers in the range

=INDEX(Sheet2!B1:B1000,MATCH(TRUE,Sheet2!B1:B1000 0,0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Lucile" wrote:

Hi,

I have a list of number starting with a bunch of 0 and I need to find the
first cell with a number different from 0. And copy this value on an other
sheet.

Any idea?
Thanks