Find the first non 0 cell in a column

  • Thread starter Thread starter Lucile
  • Start date Start date
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
 
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
 
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?
 
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
 
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...
 
Back
Top