Look in Row if find then

[cfer]

Hi All,

I would like to be able to in Sheet 1, look in each row, starting row 3, rows are dynamic, columns also may vary.

I am looking for a number 1, only a 1, as there could be more than one number 1, ie 111, in the cell.

If there is a number 1 in any cells of row 3, then copy the value in Column "C" in that row, to Sheet 2 "C5"

Go through each row in Sheet 1 till the last row, each time copy the value to the next row , Column C in Sheet 2.

Any help appreciated.

Cfer

 

[Akuini]

Try this:

Sub a926078()
Dim r As Range
Dim x As Long, i As Long, rr As Long, rc As Long

rr = Range("A" & Rows.count).End(xlUp).row
rc = Cells(1, Columns.count).End(xlToLeft).Column
i = 5
For Each r In Range(Cells(3, 1), Cells(rr, rc))
    If r.Value = 1 And r.row <> x Then
    sheets("sheet2").Cells(i, "C").Value = Cells(r.row, "C").Value
    x = r.row
    i = i + 1
    End If
Next r

End Sub

 

[cfer]

Hi Akuin

Thanks worked, made life a bit easier.

Cfer

 

[Akuini]

You're welcome.