I guess the image above explains what my macro will do. The ranges have three common regions. I want to extract the common regions out and show it in another column.
I am using a simple macro to do this and yes, the code has loops and its simple.
Note: I am assuming that you know what a Named Range is in Excel. If you don’t know what it is, please read this post first. You will understand.
Private Sub CommandButton1_Click() Call findMatchingRecords End Sub Sub findMatchingRecords() On Error GoTo ErrHandler ' Create range objects. Dim rng1 As Range Dim rng2 As Range> ' Assign both the defined ranges to the objects. Set rng1 = Sheet1.Range("region1") Set rng2 = Sheet1.Range("region2") Dim iRow As Integer iRow = 1 For Each rng1cell In rng1 For Each rng2cell In rng2 If rng1cell = rng2cell And rng1cell.Column = 1 Then ' Will check only the first column (Region). Sheet1.Cells(iRow, rng1cell.Column + 6) = rng1cell ' Show the matching data. iRow = iRow + 1 End If Next rng2cell Next rng1cell ErrHandler: Debug.Print Err.Description End Sub
First, I have created two Range objects and assigned ranges that I have created on my worksheet.
Next, I’ll iterate or loop through each cell in both the ranges using two For Each loop and compare the values. If it’s a match, I’ll write the values in another column (the 7th column).
Just remember, the records should fall inside the ranges. Make sure the named ranges are created properly.
If the two ranges are in two separate sheets, for example, region1 is in sheet1 and region2 is in sheet2, all you have to do is, assign the ranges accordingly inside the macro’s procedure.
' Assign both the ranges. Set rng1 = Sheet1.Range("region1") Set rng2 = Sheet2.Range("region2")
Thanks for reading. ☺