if greater than zero copy from the range

Question

I have data sheet with "product" list in range B38:B161 and "to order" column in range I38:I161 which will be updated manually. So when cells in range "I" is updated with number to order, the product from range "B" will be copied to range J38:J161 first empty cell. How can I achieve this?

Script I've got:

Sub copyAboveZero()

Dim sourceRng As Range
Dim cell As Range
Dim i As Long

Set sourceRng = ActiveSheet.Range("I38:I161")
i = 1

For Each cell In sourceRng
    If cell.Value > 0 Then
        cell.Resize(1, 1).Copy Destination:=Range("J" & i)
        i = i + 1
    End If
Next cell

End Sub

But this code copies only range "I" more than zero cells, while what I want is to copy cells from Range "B" and paste it to range "J" if that makes sense.

Example data:

Column B   Column I   Column J

text1      0          text2
text2      6          text4
text3      0          text5
text4      12            
text5      24            

Answer 1

The is no need to use VBA to accomplish what you are asking for.

Use the following formula in column J against each product (item): =IF(I2=0,0,B2).

This will put a value of zero in the column J when the value in the "To order" column is zero, when it is a non-zero number, it will copy the contents of cell in column B.

Answer 2

Like extensively explained in the duplicate How to split numbers into columns with negative and positive values, without empty cells? no need for any VBA. Instead, you'll need an array formula in J38:J161, such as:

=iferror(index(B38:B161, small(if(I38:I161>0, row()-37, ""), row()-37)), "")

For details, see the duplicate.