looping through a column and if cell value = Range(c4) then give value in next column

[KNKN9]

Hi,

I want to loop through a column and if the values in this cells are the same as my c4 cell value then I want all the corresponding values in column 34 in a a new column. I have the code below but all it does is give me the first value in the 34 column and repeats it.

For Each cell In Range("D:D")
If cell.Value = Worksheets("x").Range("C4").Value Then
Worksheets("y").Range("BD:BD") = cell.Offset(i, 34).Value
End If
Next


End Sub

Thanks in advance for your help

 

[KNKN9]

Hi,

I have one small adjustment to the code.

If I wanted to return the value into a different worksheet, how will I go about it ?. I have tried the code below but it only returns the first value and not all the values.

   If .Cells(x, 1).Value = C4 Then differentworksheet.Cells(17, 2).Value = .Cells(x, 38).Value

Try:

Sub Macro1()

    Dim x   As Long
    Dim LR  As Long
    Dim C4  As Variant
    
    C4 = Sheets("x").Cells(4, 3).Value
    
    Application.ScreenUpdating = False
        
    With Sheets("y")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For x = 1 To LR
            If .Cells(x, 1).Value = C4 Then .Cells(x, 56).Value = .Cells(x, 38).Value
        Next x
    End With
    
    Application.ScreenUpdating = True
                
End Sub