Find the first non 0 cell in a column

L

Lucile

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
 
S

Simon Lloyd

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 said:
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
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
M

Mike H

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
 
R

Rick Rothstein

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?
 
P

Paul C

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
 
L

Lucile

Thanks very much!

It seems to work!

Paul C said:
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
Click to expand...
 
Top