What am I missing here?

[54edser]

Hi all,

I'm scratching my head here. I have a range of data which has a list of names in Column 1 and dates in row 4. My objective is to find cells with the text "SCSL" and
copy the corresponding date and name to create a list in another sheet. The code runs, but only returns the last instance of "SCSL" in the range. That tells me something, namely
the code is skipping all the other 24 instances of "SCSL" in the range, what am I missing here?

Sub TestS()
 
    
    Dim rCell As Range, rRng As Range, b As Range, d As Range

    Set rRng = Sheets("Archive").Range("C5:AG250")
    Set b = Sheets("SCSL").Range("E1").End(xlDown).Offset(1, 0)
    Set d = Sheets("SCSL").Range("D1").End(xlDown).Offset(1, 0)

    For Each rCell In rRng.Cells
                
        If rCell.Value = "SCSL" Then
            Range(rCell.EntireRow.Address)(1, 1).Copy b
            Range(rCell.EntireColumn.Address)(4, 1).Copy d
        End If
      
    Next rCell

End Sub

 

[Joe4]

Since b and d are constantly moving down (or at least should be), you need to move those calculations into your loop before the copy commands.
Otherwise, you are only setting the b and ranges once (never to change in your code), and it keeps overwriting the value you just wrote with the next value.

 

[54edser]

Since b and d are constantly moving down (or at least should be), you need to move those calculations into your loop before the copy commands.
Otherwise, you are only setting the b and ranges once (never to change in your code), and it keeps overwriting the value you just wrote with the next value.

Hi Joe4,

Plain as the nose on my face,

Thanks very much.

 

[Joe4]

You are welcome!