1. I have Excel 2000 and I need to match the numbers in column B with the numbers in Column A. Once a match is found, then move the cells in columns B & C down to the matched row.

E.g.: In the attached spreadsheet, cell B1 matches cell A5. I need to insert cells B1:C4 down to row B5.

I would do this manually, but I have hundreds of matches like this to make.

Is there a formula to do this or do I need a macro?

Thanks in advance for the help.

2. Try this macro. Make a backup copy of your workbook first!

Code:
```Sub MatchAndShift()
Dim r1 As Long
Dim r2 As Long
Dim c As Range
r2 = 1
Do While Not Cells(r2, 2) = ""
Set c = Range("A:A").Find(What:=Cells(r2, 2), LookAt:=xlWhole)
If Not c Is Nothing Then
r1 = c.Row
If r1 > r2 Then
Range(Cells(r2, 2), Cells(r1 - 1, 3)).Insert Shift:=xlShiftDown
r2 = r1
End If
End If
r2 = r2 + 1
Loop
End Sub```

3. HansV;

Thank you very much. You helped me with this issue several years ago.

The macro worked great, but I have expanded the range. I need to shift the values in columns B:H down to match the cell in Column A that corresponds to the cell in column B. And only those columns because I have data in other columns after H (I – V) that I need to hold to the original value in row A.

I couldn’t figure out how to manipulate your macro to do this myself, so I am asking you again for help.

A new sample sheet is attached.

Dave

4. It's not very difficult. The line that shifts cells down is currently

Range(Cells(r2, 2), Cells(r1 - 1, 3)).Insert Shift:=xlShiftDown

Cells(r2, 2) is in column 2 (i.e. B) and Cells(r1 - 1, 3) is in column 3 (i.e. C). So the above line shifts cells in a range from column B to column C.

You now want to shift cells in a range from column B to column H instead of C. Column H is the 8th column. So you only need to change the 3 to 8:

Range(Cells(r2, 2), Cells(r1 - 1, 8)).Insert Shift:=xlShiftDown

The rest of the macro can remain unchanged.

5. Worked great Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•