Condition copy based on list from different sheet

[roro36]

Sheet1 has a list that is generated and variable length. I want to check each item in this list with a list on another sheet, sheet2, if there is a match, the data in column 5 on sheet2 must be copied to column 3 on sheet1.

[Bob Phillips]

With Worksheets("Sheet1")
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
        On Error Resume Next
        findrow = Application.Match(.Cells(i, "A").Value, Worksheets("Sheet2"), 0)
        On Error GoTo 0
        If findrow > 0 Then
            Worksheets("Sheet2").Cells(findrow, "E").Copy .Cells(i, "A")
        End If
    Next i
    End With

[roro36]

Thanks XLD, but it did not seem to work... I forgot to say as well that on sheet 1, the list starts at A9 to random, and on sheet2 A5 onwards.. And if A9(1) matches with A6(2), then C6(2) should be copied to E9(1)...

[Bob Phillips]

My code allows for such. Post your workbook.

[roro36]

Ok, sheet1 is cost and sheet2 is ingrdients using the above info. When B3 has been selected the list of ingrdients appears below.. I want to press cost and it should check through from A9 down and copy the costs that appear under ingredients(sheet) next to that ingredient across to coulum e or d...

[Bob Phillips]

So where is the code I gave you in that workbook? I want to see what you have done, not do it all for you.

[roro36]

Haha.. Deleted it cause it was not working... Give me a few mins to get it back how I had it...

[roro36]

Oh, it still there.. Under the macro for cost the recipe.. Right at the bottom, only with my changes to sheet names.

[Bob Phillips]

Try this change

Sub AddCost()
    Dim findrow As Long
    With Worksheets("Cost")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 3 To lastrow
            findrow = 0
            On Error Resume Next
            findrow = Application.Match(.Cells(i, "A").Value, Worksheets("Ingredients").Columns("A"), 0)
            On Error GoTo 0
            If findrow > 0 Then
                Worksheets("Ingredients").Cells(findrow, "E").Copy .Cells(i, "C")
            End If
        Next i
    End With
End Sub

[roro36]

Wham... It works.. Thanks.. One problem is it copies the formula not the value, but I guess I could try find out how to do that.. If not I'll reply here..

[roro36]

Not working... Tried .value in a few places, but it always crashes..

Worksheets("Ingredients").Cells(findrow, "E").Value.Copy .Cells(i, "F")

[Bob Phillips]

Sub AddCost() 
    Dim findrow As Long 
    With Worksheets("Cost") 
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        For i = 3 To lastrow 
            findrow = 0 
            On Error Resume Next 
        findrow = Application.Match(.Cells(i, "A").Value, Worksheets("Ingredients").Columns("A"), 0) 
        On Error Goto 0 
        If findrow > 0 Then 
            Worksheets("Ingredients").Cells(findrow, "E").Copy .Cells(i, "C") 
            .Cells(i, "C") .Value = .Cells(i, "C") .Value
        End If 
    Next i 
    End With 
End Sub

[roro36]

That added line gives an error... Is it not supposed to be part of the previous line?