Copy value from column A if value in column B at same position is X

[torsten]

Hello!

Im very new here. Have been trying for days to acomplish this.
I have e.g. 3 colums, A, B and C.
In columna A there is A number on every row.
In column B there is a number on ever row.

In column C I want to compile a new list such that if a number in column B is X (eg 1) the number from column A at the coresponding cell should be copied to column C. I want there to be no empyt cells in column C. So for the list below, the number in column A should be copied to C if its corresponding B cell is 0.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]64[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]64[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD="align: right"]62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any help!

T
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[VoG]

Try this macro

Sub test()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If .Offset(, 1).Value = 0 Then
            j = j + 1
            Range("C" & j).Value = .Value
        End If
    End With
Next i
End Sub

 

[torsten]

Thankyou very much! This was exatcly was I was after. There is one problem which I did not present. In the column A there are also som values with "0" which should not be copied. How can I exclude them?

Thanks for the great help!

Torsten

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If .Value <> 0 And .Offset(, 1).Value = 0 Then
            j = j + 1
            Range("C" & j).Value = .Value
        End If
    End With
Next i
End Sub

 

[torsten]

Works Like a charm! Thanks VoG!