VBA Copy Adjacent Cells

Question

I'm trying to write a macro that will use an if statement to copy data from one place to another.

Esentially:

If Range.Cells(i, j).Value = "Hello" Then Copy the three cells to the right of the word "Hello"

I think i might have to use an Offset? Which I'm not very experienced with, any help would be greatly appreciated! Thank you!

Edit:

This is what I have for my code:

For i = 1 To danesRange.Rows.Count
For j = 1 To danesRange.Columns.Count
If danesRange.Cells(i, j).Value = "Ins" Then
    danesRangevalues.Cells(i, j).Copy
End If
    Next j
Next i

I know how to copy and paste using VBA but I don't know how to copy and paste an unknown range because I don't know exactly where the range of those three cells will be, it depends on where the value "INS" falls. What I have so far will only copy the adjacent cell, not all three.

Where's the code you've created so far? Record a macro to copy three cells (to a separate worksheet?) to give you a start! — MiguelH
Something is bothering me: if you are going from the first column - column by column - to the last column and potentially copy a cell and the three adjacent cells (in the next three columns) then it doesn't make much sense to continue with the search in the next column (unless you want to copy redundant data). But maybe there is something I just don't yet. — Ralph
Where are you trying to put it? You already have a column reference j, so you could just set danesRange.Cells(i,j+3) = danesRange.Cells(i,j).Value or something like that. Or string them together if there are multiple cells. A solid example would be helpful. — Liss

Scott Holtzman Scott Holtzman · Accepted Answer · 2015-10-06T13:51:39

Actually, Offset with Resize is what you need here.

So change this:

    If danesRange.Cells(i, j).Value = "Ins" Then
        danesRangevalues.Cells(i, j).Copy
    End If

to this:

If danesRange.Cells(i, j).Value = "Ins" Then danesRange.Cells(i, j).Offset(,1).Resize(1, 3).Copy

So, you offset danesRange.Cells(i,j) by one column, then make the range 3 columns long.

I agree with Ralph's comment too. The way this is set up, it would seem to be copying redundant data across the columns.

VBA Copy Adjacent Cells

1 Answers