Comapre values between different ranges

[angel34]

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:

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

 

[Fluff]

This Shift:=x1Down should be Shift:=xlDown note lower case L not number one.

 

[angel34]

Bump. Hoping to get a reply.

Thanks in advance.

 

[Fluff]

You had a reply about an hour after you posted the thread, but you seem to have totally ignored it.

 

[angel34]

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]

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]

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

 

[Fluff]

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]

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]

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

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