Copy and paste data based on text in cell next to it

Question

I want the code to check every cell in in the range “A3:AAA3” for a specific text. If the cell contains that text, I want it to copy the text in the cell on the right, to two rows above (see below for illustration):

The copied text will be a date.

I have already got a piece of code which identifies every column with this text and sets the column width:

Dim c as Range
    For Each c In Range("A3:AAA3").Cells
        If c.Value = "TEXT" Then
            c.EntireColumn.ColumnWidth = 4
        End If
    Next c

I can use copy and paste if the cell is already selected:

Dim s As Range
Set s = Selection.Cells(1)

            s.Offset(0, 1).Copy
            s.Offset(-2, 0).PasteSpecial xlPasteAll

And I feel like I should be able to combine the two into something like the below so that it selects the cell with the text, and copies and pastes the cell next to it, and then loops onto the next one (something like below?), but all my attempts aren’t working – it’s not coming up with an error message, just not doing anything.

Dim c As Range

    For Each c In Range("A3:AAA3").Cells
        If c.Value = "TEXT" Then
            c.Select
            c.Offset(0, 1).Copy
            c.Offset(-2, 0).PasteSpecial xlPasteAll
            End If
    Next c

Thoughts? I’m sure it’s a very simple solution but I’m a bit stuck.

Although there's no need to loop or select your code should work if the text matches exactly (e.g. no trailing spaces). — SJR, Apr 15 '20 at 13:48

Pᴇʜ · Accepted Answer · 2020-04-15T13:51:59.600

The c.Value = "TEXT" will check if the value is exactly TEXT but in your example it is 1 TEXT so it is only like TEXT. So we need to use a place holder and the like operator.

Dim c As Range
For Each c In Range("A3:AAA3").Cells
    If c.Value Like "*TEXT*" Then
        c.Offset(0, 1).Copy Destination:=c.Offset(-2, 0)
    End If
Next c

Note that you can copy/paste in one line.
And you don't need c.Select (see How to avoid using Select in Excel VBA.)

Note that Excel does not know in which worksheet this range is Range("A3:AAA3").Cells better specify the worksheet like ThisWorkbook.Worksheets("MySheet").Range("A3:AAA3").Cells

Finally in all cases it is helpful if you debug your code step by step using F8 so you can see what your code does in every step and investigate the values of your variables to see where exactly it goes wrong.

Off Topic:

I would even prefer the following style:

c.Offset(ColumnOffset:=1).Copy Destination:=c.Offset(RowOffset:=-2)

which reads more intuitive.

Ah! I was so close, how annoying. Thanks so much - it works perfectly. — Liz H, Apr 15 '20 at 14:05

Copy and paste data based on text in cell next to it

1 Answers1