Hi Joel,

i have added 2 more lines of code, it works for the following condition ie
when
sheet1 reqdqty is greater than the totalqty of sheet2
but i want to insert the row when second time the item appears.
and i donno what to write when the sheet1 reqdqty is less than the totalqty
of sheet2
one more thing is if the item is not available in sheet2 i want that control
to go and search in sheet3 (which i have not mentioned)

Sub getdata()

RowCount = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) < ""
newitem = .Range("A" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=newitem, LookIn:=xlValues)
If c Is Nothing Then
Range("A" & RowCount).Interior.ColorIndex = 4
ElseIf Range("A" & RowCount).Offset(0, 1) c.Offset(0, 3) Then

store1 = c.Offset(0, 1)
Sheets("Sheet1").Range("C" & RowCount) = store1
Store2 = c.Offset(0, 2)
Sheets("Sheet1").Range("D" & RowCount) = Store2
ElseIf Range("A" & RowCount).Offset(0, 1) < c.Offset(0, 3) Then
........
........'i donno what to write here
........'if the quantity is not enough or the item is not there
then goto sheet3

End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub

"Joel" wrote:

Sub GetData()

RowCount = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) < ""
NewItem = .Range("A" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=NewItem, LookIn:=xlValues)
If Not c Is Nothing Then
Store1 = c.Offset(0, 1)
Sheets("Sheet1").Range("C" & RowCount) = Store1
Store2 = c.Offset(0, 2)
Sheets("Sheet1").Range("D" & RowCount) = Store2
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub


"Ren" wrote:

hi,
i have two sheets, i want the item to be matched and quantity should be
placed in store1 or store2 of sheet1 based on the location in sheet2
how can i include this inside a For Each loop??
or If else condition


item requiredQty store1 store2
apple 6
apple 7
kiwi 5
guava 10

item store1 store2 total
orange 3 2 5
apple 5 8 13
guava 10 12 22


thanks
ren