PDA

View Full Version : Condition copy based on list from different sheet



roro36
02-06-2012, 06:47 AM
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
02-06-2012, 07:12 AM
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
02-07-2012, 12:26 AM
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
02-07-2012, 02:22 AM
My code allows for such. Post your workbook.

roro36
02-08-2012, 04:59 AM
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
02-08-2012, 05:06 AM
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
02-08-2012, 05:09 AM
Haha.. Deleted it cause it was not working... Give me a few mins to get it back how I had it...

roro36
02-08-2012, 05:10 AM
Oh, it still there.. Under the macro for cost the recipe.. Right at the bottom, only with my changes to sheet names.

Bob Phillips
02-08-2012, 05:42 AM
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
02-08-2012, 05:53 AM
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
02-08-2012, 06:31 AM
Not working... Tried .value in a few places, but it always crashes..

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

Bob Phillips
02-08-2012, 08:16 AM
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
02-08-2012, 09:58 PM
That added line gives an error... Is it not supposed to be part of the previous line?