Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fetch the data from sheet2 & place it in sheet1 based on the locat
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fetch the data from sheet2 & place it in sheet1 based on the locat
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fetch the data from sheet2 & place it in sheet1 based on the l
Hi Joel, thank you so much for your time,
the code is working, but my challenge is when the item is found it should match the required quantity(sheet1) with the total quantity of sheet2.(to check whether enough qty is available) sheet1:(ex:apple appears many times) item requiredqty store1 store2 apple 6 5 1 apple 4 0 4 (when the apple appears next time or any no of times) sheet2(ex:apple appears only once) when the item matched for second time,it should take the quantity from the balance quantity,and edit the quantity supplied and left in next row as following item store1 store2 total qtysupplied balanceleft apple 5 10 15 6 9 4 5 thank you so much joel Ren "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
fetch the data from sheet2 & place it in sheet1 based on the l
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
fetch the data from sheet2 & place it in sheet1 based on the l
I made some changes to the code. These changes are not complete because you
didn't give me enough instructions what you wanted done. Don't understand your remark when you want to insert a new row. Some how you need to check if quantity has been pulled when an item appears more than once. You should have new columns on sheet2 indicating the quantity used. 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) End With If c Is Nothing Then QuantNeeded = .Range("B" & RowCount) Else Store1 = c.Offset(0, 1) Store2 = c.Offset(0, 2) .Range("C" & RowCount) = Store1 .Range("D" & RowCount) = Store2 If .Range("B" & RowCount) = _ (Store1 + Store2) Then QuantNeeded = 0 Else QuantNeeded = .Range("B" & RowCount) - _ (Store1 + Store2) End If End If If QuantNeeded 0 Then With Sheets("Sheet3") Set c = .Columns("A:A").Find(what:=newitem, _ LookIn:=xlValues) End With If c Is Nothing Then .Range("A" & RowCount). _ Interior.ColorIndex = 4 Else Store1 = c.Offset(0, 1) Store2 = c.Offset(0, 2) End If End If RowCount = RowCount + 1 Loop End With End Sub "Ren" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
Automatic populate Sheet2 with data from Sheet1 based on criteria. | Excel Discussion (Misc queries) | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
Function on Sheet1 based on data on Sheet2 | Excel Worksheet Functions | |||
copy data from sheet1 based on criteria in sheet2 to sheet3 | Excel Programming |