Creating a Loop from a Recorded Macro

Question

I have 2 sets of data that have to be matched based on one identifier that they both have in common (Range 1 is on Sheet1 and it runs from Column A:F, Range2 is on Sheet3 and runs from Column A:M). The matching values for both ranges will occur in Column E for Sheet1 and Column C for Sheet3. I tried to record a macro to see if I could create a simple loop to just repeat what I was doing until it ran into any irregular data, but I ran into the issue of how to loop the actions that I was doing. Here is my code:

Sub Record_And_Destroy() 
    'first issue is writing a loop that will cycle through all rows in column E
    'starting in row 18 in this example
    Range("E18").Select
    Selection.Copy
    Sheets("Sheet3").Select
    'Sheet3 contains the second table of data 
    'I want to search based on the copied value from Sheet1...
    *Cells.Find(What:="03885740-131601", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate*
    'The data that is being matched is in column C of Sheet3 but I need all columns A:M that
    'are associated with the found match
    Range("A10:M10").Select
    Application.CutCopyMode = False
    Selection.Cut
    Sheets("Sheet1").Select
    Range("G18").Select
    'Column G is the next available column when matching to Sheet1 so every other selection
    'would be placed in Column G but the row would differ based upon
    'which row was being searched
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Selection.Delete Shift:=xlUp
    'this way I clean up the data base to only contain the "problem" cases
End Sub

Question 1: is there a way to use Cells.Find to search for the selection as opposed to the value?

Question 2: In the event that the loop found no matches, is there a way to format rows G:S on Sheet1 to display a red background so that I would know to go back and check those values once the loop had concluded?

If you have Excel 2010 or higher you can reference another worksheet in a formula for a conditional formatting rule to color Sheet1's non-matching rows red. — , May 19 '15 at 16:37

score 1 · Accepted Answer · edited May 23 '17 at 12:05

As I understand it, you want to go down column E (starting at row 18) and search each value to see if it exists in Sheet3's column C. If it exists then copy that row on Sheet3 from column A to column M and put it into Sheet1 starting at column G on the same row currently being examined.

Sub Record_And_Destroy()
    Dim rw As Long, mrw As Long, ws3 As Worksheet

    Set ws3 = Sheets("Sheet3")

    With Sheets("Sheet1")
        For rw = 18 To .Cells(Rows.Count, "E").End(xlUp).Row
            If CBool(Application.CountIf(ws3.Columns(3), .Cells(rw, "E").Value)) Then
                mrw = Application.Match(.Cells(rw, "E"), ws3.Columns(3), 0)
                ws3.Cells(mrw, "A").Resize(1, 13).Copy _
                  Destination:=.Cells(rw, "G")
                ws3.Rows(mrw).EntireRow.Delete
            End If
        Next rw
    End With

    Set ws3 = Nothing

End Sub

Note that I've completely avoided the use of .Select in favor of direct worksheet and cell addressing. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goal(s).

This is exactly what I needed and it worked perfectly! Thank you for your help! I will keep this example on-file for future similar cases. You're amazing! — user3794203, May 19 '15 at 19:46

Creating a Loop from a Recorded Macro

1 Answers1