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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
This Shift:=x1Down should be Shift:=xlDown note lower case L not number one.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You had a reply about an hour after you posted the thread, but you seem to have totally ignored it. ;)
 

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68

ADVERTISEMENT

I acknowledge that it was a silly typographical error in my post. I did not make a reply just because I was expecting a reply offering a solution to the query and contemplated covering this comment in my feedback. I never ignored your comment and apologize if it created any unpleasantness.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
It didn't create any unpleasantness, but you gave us virtually no information & as you never commented on the typo we had no way of knowing if that resolved the problem or not.
What sort of values do you have in cols D & S?
Also is col Y Passed as opposed to passed or PASSED?
 

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68

ADVERTISEMENT

Columns D and S contains text I.e. names of parties. Column Y contains comments 'Passed' or 'In process' or 'Withheld'
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
In that case if no rows are being added it would suggest that the values in D & S are not the same.
Check that there are no leading/trailing spaces.
Also the code is case sensitive so that it would consider angel & Angel to be different words
 

angel34

Board Regular
Joined
Jun 3, 2016
Messages
68
I understood your point but the values have been copied from a common database and can be assumed to be without leading/trailing spaces. For testing the code, I have copied from col D to col S to eliminate all possible mis-matches you pointed out but the code inserts multiple rows and copies the same values multiple times for a single match. The expected result is to insert a single row and copy the range only once for a particular match and proceed to the next match. But unfortunately, that does not happen.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
the code inserts multiple rows and copies the same values multiple times for a single match.
This is the first time that you have said what the problem is. For future reference it's best to tell us things like this to begin with. ;)
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 i = 59 To 9 Step -1
         If .Exists(Cells(i, 4).Value) Then
            Rows(i + 1).Insert
            .Item(Cells(i, 4).Value).Resize(, 5).Copy Cells(i + 1, 4)
         End If
      Next i
   End With
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,633
Messages
5,626,007
Members
416,151
Latest member
Openminded intellectual

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