vba copy cell until blank loop

[EMcK01]

Hi,

I am looking to copy a date for a range, then a different date for a 2nd range and repeat this until all the dates that are found in the column have been copied next to the event.

I can find the date and copy it to where I like, however I'm unsure how to copy it down for that set range then how to step once it finds a blank row to start the next range.

The code below is what I have so far.

Set rngB = Sheets(wsName).Range("B3:B1000")       For Each cell In rngB
            If cell.Value Like "*day*" Then
               cell.Copy cell.Offset(1, 3)
            End If
        Next cell

Any help would be appreciated.

Thanks,
EMcK

 

[decadence]

Can you explain a bit more as you are saying "dates" but you code is looking for "*day*".

 

[EMcK01]

thanks for the reply.

The dates are currently shown as Saturday 16 June 2018 for example, I run a something later to get rid of the day and keep the date part only, my intention is to change it to short date, i.e "16-Jun" however I found it useful while doing other formatting as its a unique part to search for.

 

[decadence]

The code you have posted originally doesn't work how you have it, If you want to step through B3:B1000 to add dates

Try this...

    Dim RngB As range
    Set RngB = range("B3:B1000")
        For Each cell In RngB
            If cell.Value Like "*day*" Then
               cell.Copy cell.Offset(1, 3)
            End If
        Next cell

However i am slightly confused as to what you are after, You are saying you are running something later to remove the day but keep the date, then you want to change it to short date but you still want to keep the Long Date for formatting. Can you explain what you are trying to do and post the code so we can understand better