Comapre values between different ranges

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68
I am trying to compile a macro that compares each cell in Range D9:D60 to Range S9:S14. In case

a match is found, the macro should insert a row below the matched cell and copy a part of the

row (from column S to column W) in the inserted row at Column D. For example, if cells D10 and

S12 is a match and Y12 value is "Passed", a row should be inserted at D11 and range S12:W12

needs to be copied at D11. There may be multiple matches in column D, but this action needs to be

taken only on the first match.

I have the following code which doesn't give out an error but doesn't seem to work either.I

presume I may be quite close but unable to figure out the mistake I am making.

Any help will be much appreciated.
Code:
VBA Code:
Dim c As Range
Dim e As Range

For Each c In Range("D9:D59")
For Each e In Range("S9:S14")
    If c.Value = e.Value And Range("Y" & e.Row).Value = "Passed" Then
    Range("C" & c.Row).Offset(1,0).EntireRow.Insert Shift:=x1Down
    Range("S" & e.Row & ":W" & e.Row).Copy Worksheets("Stock").Range("D" & c.Row).Offset(1, 0)
Exit For
End If
Next e
Next c

End Sub
 

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68
This code works perfectly. Thank you very much. I marked this answer as the solution. One last question. I did not understand how to code the criteria when multiple matches have been found for the same value. For example, S10 has 2 matches in D15 and D21. Then rows will be inserted below both D15 and D21. But I need this only in the first match i.e. D15. How to take take care of this in the code. I mentioned this criteria in my original post.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
As long as you don't have anything below D59, try
VBA Code:
Sub angel()
   Dim Cl As Range
   Dim i As Long

   With CreateObject("scripting.dictionary")
      .comparemode = 1
      For Each Cl In Range("S9:S14")
         If Not .Exists(Cl.Value) And Cl.Offset(, 6) = "Passed" Then .Add Cl.Value, Cl
      Next Cl
      For Each Cl In Range("D9:D59")
      Cl.Interior.Color = 30159
         If .Exists(Cl.Value) Then
            Rows(Cl.Row + 1).Insert
            .Item(Cl.Value).Resize(, 5).Copy Cl.Offset(1)
            .Remove Cl.Value
         End If
      Next Cl
   End With
End Sub
 

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68
This worked great yet again. Currently, I do not have data below D59. In case, I start to have data below D59, may I ask, whether there is a risk of getting the data overwritten. I have noticed during execution of this code that the interior color of Column D changes to Orange. Can I use a command in the code in the line of
VBA Code:
Range ("D9:D59"). Interior.Color=xlNone.

Thank you once again for guiding me all through in solving this problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Oops delete this line
VBA Code:
Cl.Interior.Color = 30159
I just put that in to test something.
Nothing will get overwritten, so there is no problem there.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,652
Messages
5,626,093
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top