-1

I would like to take values from column A and cut and paste them into column B, with each value exactly one cell to the left of their corresponding matching values from column C. Here is a before and after of what I would like to accomplish. Basically, each value from column A finds its match in column C and is copied, then pasted directly to the left of its match in column B.

Column A      Column C
10               1
9                2
8                3
7                4
6                5
5                6
4                7
3                8
2                9
1                10

Column B       Column C
1                1
2                2
3                3
4                4
5                5
6                6
7                7
8                8
9                9
10               10

Here it what I have tried:

Sub arrange()
Cells(1, 1).Activate

Do

If IsEmpty(ActiveCell) Then Exit Do
If ActiveCell.Offset(0, 2).Value = ActiveCell.Value Then
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Activate
Else
ActiveCell.Offset(1, 0).Activate


End If

Loop

End Sub

The problem with this approach is that it only finds matching values in the same row. I want it to be able to search the entire column and place the value next to a match, whether the match is in the same row or not.

chicoexcel
  • 5
  • 3
  • What is your question? What have you tried? Welcome to StackOverflow. Please read this, http://stackoverflow.com/help/how-to-ask, and follow the guidelines for asking a question. That'll greatly improve the chances that your question will get answered. – jbrown Nov 17 '16 at 22:11
  • Sub arrange() Cells(1, 1).Activate Do If IsEmpty(ActiveCell) Then Exit Do If ActiveCell.Offset(0, 2).Value = ActiveCell.Value Then ActiveCell.Select Selection.Copy ActiveCell.Offset(0, 1).Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Activate Else ActiveCell.Offset(1, 0).Activate End If Loop End Sub – chicoexcel Nov 17 '16 at 22:15
  • 1
    Great! Please update your question with this code so that it doesn't get lost in the comments thread – jbrown Nov 17 '16 at 22:20
  • I have a feeling (more than a feeling actually) that some of your issue stems from using `.Select`. [It's highly recommended to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as it can cause many unforseen quirks. – BruceWayne Nov 17 '16 at 22:48

1 Answers1

0

What you need to do is loop through column A and check to see if each value can be found in column C. If it is copy to column B.

Sub arrange()

Dim Wbk As Workbook

Set Wbk = ActiveWorkbook

Dim row As Integer
Dim col As Integer
Dim currentA As Integer

Dim numRows As Integer

Worksheets("chicoexcel").Activate
numRows = Wbk.Worksheets("chicoexcel").Range("A2", Range("A2").End(xlDown)).Rows.Count

For a = 1 To numRows + 1 'start at 1 because it is the first row. This will loop through Column A

    currentA = Sheets("chicoexcel").Cells(a, "A").Value 'Save the current value in column A

    For c = 1 To numRows + 1 'start at 1 because it is the first row. This will loop through Column C

        If (Sheets("chicoexcel").Cells(c, "C").Value = currentA) Then 'Check if the col C value is equal to the current col A value.
            Sheets("chicoexcel").Cells(c, "B") = Sheets("chicoexcel").Cells(c, "C").Value  'If so copy to column B
            Sheets("chicoexcel").Cells(a, "A") = Null  'Remove the value from col A
        End If

    Next c

Next a


End Sub

I assumed all the values were integers, and that the data starts in row 1.

This is not the most efficient solution, but you'll be able to see what is happening using the debugger. You could include the FIND function to speed things up. I'll let you figure that out yourself.

dev1998
  • 882
  • 7
  • 17
  • So I gave your macro a try and it was able to move the matching values over, but I need the matching value to be moved adjacent to its match from column C. For example, as shown above in my original post, I have the value 10 in cell A1. Its match is found in C10, so I would need the value of 10 in cell A1 to be moved to cell B10 to be adjacent to its match, the value of 9 from cell A2 to be moved to cell B9, value of 8 from cell A3 to cell B8 and so on with all of the values in column A. – chicoexcel Nov 18 '16 at 20:48
  • OK. Try it again. I made an update. I was putting the value into the wrong row. – dev1998 Nov 18 '16 at 22:07