What am I missing here?

54edser

New Member
Joined
Sep 3, 2014
Messages
4
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?



Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
You are welcome! :)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top