SalmanMuneer
New Member
- Joined
- Jan 19, 2013
- Messages
- 1
Hello All,
Am new to this forum so a big Helloooo to all.
I am a new to VBA programming. I use vlookups and pivots etc and some basic macros but I am not much familiar with ranges, offsets and other object functions.
Vlookups do help solve the problem but with ever increasing search queries and variable data coming in I would really really love to have some search macro that solves the issue. May be it will give a starting point for me to develop my vba skills on how to work with loops, ranges etc and further improve searching as well.
I have attached 3 Work books with dummy data:
Work Book 1 named “Search List” contains two sheets.
Sheet 1 named as “search list” is what is provided by various stakeholders. Each entry has a unique code.
Sheet 2 named “Final” is where the macro needs to collate all the data.
Work book 2 named “All Product Cost” is a big sheet that has all past current products and costs etc and entries have a unique code as well.
Work book 3 named “Web Price” is data from a web scrap tool however for one product from one shop and date there can be multiple websites and with different prices which all need to be copied.
Functionality
The macro needs to be in work book 1.
The macro or function will pick code from cell A2 from sheet “Search List” in Work Book 1, and search in Work Book 3 column A. Once a record is found for the selected code it needs to copy range B2 to E2 of the search code A2 in “Search List” sheet Work Book 1 and paste it in sheet “final” of Work Book 1 and also need to copy the column E (Site) and F (Price) values which macro found in Work Book 3 and paste in the final sheet.
The macro needs to perform the same search again for A2 and do the same procedure if another record is found in Work Book 3 and continue until no record is found. After that it will pick Code in A3 and search again and continue copying until no more codes are available in column A of “Search List” sheet in Work book 1.
The costs from Work Book 2 can easily be copied into Final sheet through a vlookup.
The final sheet will then have all data which can be pivoted to see all web sites their prices, and our own product costs.
Not sure how easy it is and how slow it will make the process as the search list will be quite big and data sets are also quite big but if it is possible then it will solve a nightmare.
Not sure how to attach the excel files here
Work Book 1 "Search List" Sheet
[TABLE="width: 430"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Flag[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On sale[/TD]
[/TR]
[TR]
[TD]ScrewDriver41394Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]On Promo
[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 1 "Final" Sheet
[TABLE="width: 464"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Flag[/TD]
[TD]Cost[/TD]
[TD]Website[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]X[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]X[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]Y[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]On Promo[/TD]
[TD]12[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 2 "Product Costs" Sheet
[TABLE="width: 485"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop C[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41389Shop B[/TD]
[TD]Hammer[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41389Shop A[/TD]
[TD]Hammer[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]ScrewDriver41389Shop A[/TD]
[TD]ScrewDriver[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]ScrewDriver41394Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop A[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop A[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop B[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop B[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop C[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Drill41384Shop A[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Drill41384Shop B[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]Drill41384Shop C[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Drill41389Shop B[/TD]
[TD]Drill[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]Drill41389Shop A[/TD]
[TD]Drill[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]Tape41389Shop A[/TD]
[TD]Tape[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tape41394Shop C[/TD]
[TD]Tape[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop A[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop B[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop C[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop C[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 3 "Web Price" Sheet
[TABLE="width: 396"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Site[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]X[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]X[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]R[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]S[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]S[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop C[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]T[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Tape41453Shop B[/TD]
[TD]Tape[/TD]
[TD]28-Jun-13[/TD]
[TD]Shop B[/TD]
[TD]U[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tape41453Shop B[/TD]
[TD]Tape[/TD]
[TD]28-Jun-13[/TD]
[TD]Shop B[/TD]
[TD]F[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]Y[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Tape41488Shop A[/TD]
[TD]Tape[/TD]
[TD]02-Aug-13[/TD]
[TD]Shop A[/TD]
[TD]H[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Am new to this forum so a big Helloooo to all.
I am a new to VBA programming. I use vlookups and pivots etc and some basic macros but I am not much familiar with ranges, offsets and other object functions.
Vlookups do help solve the problem but with ever increasing search queries and variable data coming in I would really really love to have some search macro that solves the issue. May be it will give a starting point for me to develop my vba skills on how to work with loops, ranges etc and further improve searching as well.
I have attached 3 Work books with dummy data:
Work Book 1 named “Search List” contains two sheets.
Sheet 1 named as “search list” is what is provided by various stakeholders. Each entry has a unique code.
Sheet 2 named “Final” is where the macro needs to collate all the data.
Work book 2 named “All Product Cost” is a big sheet that has all past current products and costs etc and entries have a unique code as well.
Work book 3 named “Web Price” is data from a web scrap tool however for one product from one shop and date there can be multiple websites and with different prices which all need to be copied.
Functionality
The macro needs to be in work book 1.
The macro or function will pick code from cell A2 from sheet “Search List” in Work Book 1, and search in Work Book 3 column A. Once a record is found for the selected code it needs to copy range B2 to E2 of the search code A2 in “Search List” sheet Work Book 1 and paste it in sheet “final” of Work Book 1 and also need to copy the column E (Site) and F (Price) values which macro found in Work Book 3 and paste in the final sheet.
The macro needs to perform the same search again for A2 and do the same procedure if another record is found in Work Book 3 and continue until no record is found. After that it will pick Code in A3 and search again and continue copying until no more codes are available in column A of “Search List” sheet in Work book 1.
The costs from Work Book 2 can easily be copied into Final sheet through a vlookup.
The final sheet will then have all data which can be pivoted to see all web sites their prices, and our own product costs.
Not sure how easy it is and how slow it will make the process as the search list will be quite big and data sets are also quite big but if it is possible then it will solve a nightmare.
Not sure how to attach the excel files here
Work Book 1 "Search List" Sheet
[TABLE="width: 430"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Flag[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On sale[/TD]
[/TR]
[TR]
[TD]ScrewDriver41394Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]On Promo
[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 1 "Final" Sheet
[TABLE="width: 464"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Flag[/TD]
[TD]Cost[/TD]
[TD]Website[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]X[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]X[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]On Sale[/TD]
[TD]20[/TD]
[TD]Y[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]On Promo[/TD]
[TD]12[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 2 "Product Costs" Sheet
[TABLE="width: 485"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop C[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41389Shop B[/TD]
[TD]Hammer[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41389Shop A[/TD]
[TD]Hammer[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]ScrewDriver41389Shop A[/TD]
[TD]ScrewDriver[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]ScrewDriver41394Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop A[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop A[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop B[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop B[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]ScrewDriver41399Shop C[/TD]
[TD]ScrewDriver[/TD]
[TD]05-May-13[/TD]
[TD]Shop C[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Drill41384Shop A[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Drill41384Shop B[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]Drill41384Shop C[/TD]
[TD]Drill[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Drill41389Shop B[/TD]
[TD]Drill[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]Drill41389Shop A[/TD]
[TD]Drill[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]Tape41389Shop A[/TD]
[TD]Tape[/TD]
[TD]25-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tape41394Shop C[/TD]
[TD]Tape[/TD]
[TD]30-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop A[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop B[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tape41399Shop C[/TD]
[TD]Tape[/TD]
[TD]05-May-13[/TD]
[TD]Shop C[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Work Book 3 "Web Price" Sheet
[TABLE="width: 396"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Site[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]X[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]X[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]R[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]S[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop B[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop B[/TD]
[TD]S[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop C[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop C[/TD]
[TD]T[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Tape41453Shop B[/TD]
[TD]Tape[/TD]
[TD]28-Jun-13[/TD]
[TD]Shop B[/TD]
[TD]U[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tape41453Shop B[/TD]
[TD]Tape[/TD]
[TD]28-Jun-13[/TD]
[TD]Shop B[/TD]
[TD]F[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Hammer41384Shop A[/TD]
[TD]Hammer[/TD]
[TD]20-Apr-13[/TD]
[TD]Shop A[/TD]
[TD]Y[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Tape41488Shop A[/TD]
[TD]Tape[/TD]
[TD]02-Aug-13[/TD]
[TD]Shop A[/TD]
[TD]H[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
