Find the first non 0 cell in a column

[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

 

[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

 

[Lucile]

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

Thanks

 

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

 

[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

 

[Lucile]

Thanks very much!

It seems to work!

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