Loop through cells compare values add and move to another sheet

**begilhang** · 03-15-2010, 02:38 AM

I am trying to allocate products from different suppliers to customers. For this I have an algorithm that I am trying to implement in excel. What I am trying is to place data from sheet data gathering allocation to ProposedTerm sheet. What I am trying to achieve is the following.
We have 4 cases but they all require the same procedure.
1- Same supplier can give same product description
2- Same supplier can give different product descriptions
3- Different suppliers can give same product description. We allocate product descriptions starting from first supplier and when capacity is finished we switch to other supplier capacity. In this case customer may get product from more than 1 supplier. Look at example workbook please in ProposedTermSheet.
4- Different suppliers can give different product descriptions

I think I need a loop that checks each cell in range for same product descriptions in DataGatheringAllocation sheet in column d. If exists than the sum of their total capacity in column L must be placed in ProductDescription Sheet j4:j along with their respective Product Names, KG/PCs

1. Data from DataGatheringAllocationSheet column c9 (Product Names)till end of activecell to ProposedTermSheet column e4 till down.
2. Data from DataGatheringAllocationSheet column d9 (Product Description)till end of activecell to ProposedTermSheet column f4 till down.
3. Data from DataGatheringAllocationSheet column e9 (Supplier) till end of activecell to ProposedTermSheet column d4 till down.
4. Data from DataGatheringAllocationSheet column f9 (KG/PCs)till end of activecell to ProposedTermSheet column h4 till down.
5. Data from DataGatheringAllocationSheet column L9 (Tot.Supply Capacity) till end of activecell to ProposedTermSheet column j4 till down.

Each cell in ProposedTermSheet represents a supplier and its product description from DatGatheringAllocation sheet. To begin with we need total supply capacity of that product description in column j4 in Proposed term sheet. Once product is allocated to customer, cellJ5 = J4-O4 (allocated amount to customer ). Allocated amount is taken from the ProductAllocation sheet, user enters demand of each customer manually in (ProductAllocationSheet) column I3, M3, Q3, U3, Y3, AC3 (Always offset 4 columns to right) for each product description. The customer information is copied to proposed term sheet in column N transposed.
My explanation may not be very clear but once you look at the example sheet I think you will understand what I mean. I did color coding for it to be easy read. Looking forward for valuable help.
Thank you very much.

**begilhang** · 03-15-2010, 09:59 AM

Sorry for double post, was trying to edit.

**begilhang** · 03-22-2010, 04:07 AM

Hi ,
I think my post was not very clear. I will try to explain it step by step. Excuse my coding, as there are too many loops, I know I have too much mistakes. It is just illustrative to show you what I am trying to achoieve
We can consider it in terms of modules

‘Will copy the value from DataGatheringAllocation sheet to ProposedTerm Sheet. (Step 1 sheet in example workbook)
LtRow = [A65536].End(xlUp).Row
For i=9 to ltrow
For r=4 to ltrow
Set wsDA = Worksheets("DataGatheringAllocation")
Set wsPT = Worksheets("ProposedTermSheet")
‘Values to be copied are
wsDA.cells(i,5).select
wsPT .cells(r,4).paste
wsDA.cells (i,4).select
wsPA. cells (r,6).paste
wsDA. cells (I,3).select
wsPA. cells (r,5).paste
wsDA.cells(i,6).select
wsPA. cells (r,8).paste
wsDA. cells (i,12) .select
wsPA cells (r,10).paste
Next i
Next r

The desired result is in the step1 sheet in the attached workbook

Module 2 (Step2 sheet in example workbook)
‘Find how much of each product description is is supplied by each supplier
LtRow = [A65536].End(xlUp).Row
For i=4 to ltRow

In ProposedTermSheet Considers Cells(i,4) and (i,6) together. Cells(i,4) is compared with each cell in range (“D”) and cells(i,6) is compared with each cell in range(“F”). When there is a repetition, than cells(i,10).value is added to cells(4,10).value (to find total supply capacity) and repeated row is deleted.
The desired result is in sheet step2 of the attached workbook

Module 3 (Step3 sheet in example sheet)
‘This module is the last one where the allocation and calculations are made. Each product description is allocated to each customer in the given order.

Set wsPT = Worksheets("ProposedTermSheet")
Set wsPA = Worksheets("ProductAllocation")
ltRow = [A65536].End(xlUp).Row
ltCol= Cells(1, 256).End(xlToLeft).Column
For a=3 to ltrow
For b=4 to ltrow
For c= 4 to ltrow
For d=9 to ltcol

If wsPA.cells(a,2).value=wsPT.cells(b,6).value then
wsPT.cells(c,14)=wsPA.cells(2,d) and wsPT.cells(c,15)=wsPA.cells(e,f)
Do
For e=3 to lt ltrow
For f=9 to ltcol
if wsPA.cells(e,26).value>wsPT.cells(e,f) then row(“e+1:e+1”).select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
wsPT.cells(e+1,f)=wsPT.cells(e,f).value-wsPT.cells(c,15).value
End If
Loop Until wsPA.cells(e,26).value=wsPT.cells(e,f)
F=f+4

I am not sure how to do the rest. I hope it is clear. The final result is step3. which should be in ProposedTermSheet.
Looking forward for some help.
Thx