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

 

[JackDanIce]

It's not entirely clear.

What is the name of the sheet Range("D:D") is in?
In what row does the offset value go into, in worksheets("y") column BD? E.g. what is value of i in above and where does i change in your code - inside the loop or outside?

 

[Roger Govier]

Maybe something like

Sub CopyData()
    Dim i As Long, lr As Long
    With Worksheets("x")
        lr = .Cells(Rows.Count, 4).End(xlUp).Row
        For i = 1 To lr
            If .Range("D" & i).Value = .Range("C4").Value Then
                Worksheets("y").Range("BD" & i) = .Range("D" & i).Offset(i, 34).Value
            End If
        Next
    End With
End Sub

 

[KNKN9]

Hi,

Please see my response below

It's not entirely clear.

What is the name of the sheet Range("D:D") is in?
say for instance this is in worksheet(Y)
In what row does the offset value go into, in worksheets("y") column BD? E.g. what is value of i in above and where does i change in your code - inside the loop or outside?

i is from first to last row.

 

[KNKN9]

Thanks for you response. This did not seem to work for me.

 

[JackDanIce]

Still not clear, try by testing on a copy of your file:

Sub Macro1()
    
    Dim x       As Long

    Application.ScreenUpdating = False
    
    With Sheets("y")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 4).End(xlUp).Row
        With .Cells(1, 4).Resize(x)
            .AutoFilter field:=1, Criteria1:=Sheets("x").Cells(4, 3).Value
            .Offset(1, 52).Resize(x - 1, 1).SpecialCells(xlCellTypeVisible).Value = .Offset(1, 34).Resize(x - 1, 1).SpecialCells(xlCellTypeVisible).Value
        End With
        .AutoFilterMode = False
    End With
        
    Application.ScreenUpdating = True
    
End Sub

A properly explained example with actual values and figures as if you're talking to someone who cannot see your PC monitor at all would help if above doesn't work.

 

[KNKN9]

To be more clear ...

worksheet 1
c4 = " a"

wksheet 2
A B C
x 1
x 2
y 3
z 4


I need to check wksheet2 column A values and if this cell values is the same as wksheet1 c4 then i need to corresponding values in wksheet2 col B put into a new column.

 

[JackDanIce]

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

 

[KNKN9]

Perfect !...

This does the job


Much Appreciated !

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

 

[JackDanIce]

You're welcome