inventory control

[atkinsonmuldo]

I am working on material requirement planning that is why I have to deal with invetory values. I have many products and they have specific stock values. Firstly I am looking at my current situation for the products. When I need a product I am looking at its stock value then I compare it with its delivery time. I have to know how long I can go with my stock value. SO at the below I am giving an exmp of one sheet of my project(delivery time and value sheet).
We will suppose that I am looking at AB2002 and its stock value is1300.

I want to create macro which is about to sum distance but this distance will up to me.

Exp:

A B C D E F
1 Product Aug.23 Aug.24 Aug.25 Aug.26 Aug.27
2 AB2001 150 265 452 1900
3 AB2002 125 987 452
4 AB2003 1908 125 608
5 AB2004 50 240


I want to create macro to sum rows which are next to AB2002 and till the empty row. In my example it is B3:C3-but it would be changed on the other product, so the cells has to be changeable-. I want to attend this sum to one object for example "table".

dim table as long
table=Sum.........

and as I mentioned before our stock value is 1300 and I will compare "table" with 1300. If "table"<1300 then I will continue to sum the other full cells which are nest to AB2002 so it will be B3:E3. Then now "table" will be new sum. I will compare it again with 1300. If it is <1300 then continue sum, else I will order this product on its last full cell's delivery time which is the columns headline.It will be Aug.26 for my exp.

Now I stuck at this point, could you help me for this situation,pls?

[Marcol]

Hi atkinsonmuldo
Welcome to the forum.

I assume you have a workbook in progress.
You would do best to post a sample of this workbook.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

[atkinsonmuldo]

Hi again,

Firstly thank you for your attention. I load my excel workbook. It has 2 sheets. first is MRP and the other delivery. My macro starts with looking at the Current situation column at MRP sheet. If it is <0 then it shows me that I have to order it and have to look at the deliverysheet to determine my order date. Because at the same time I have stocks for that product. from delivery sheet I can decide how long I can go with my stock for this product.

For example we can look at the AB2001 from MRP sheet.Its current situation is -1200 and it is <0 that is why I am looking at delivery sheet for its order time. At mRP sheet its stock value is 1567. After take this stock value from MRP sheet I am looking at delivery sheet.I am starting to look B2. I can afford it with my stock value then B3. I sum till my stock value < my sum.For example in my example it is B2:E2. It is 2767 so it is >1567. Then I am looking at its delivery time, it is Aug,26. Then I am taking that date and turn my MRP page and write it on delivery time column of this product. Then off course I determine its value with palette value but it is easier. Firstly I want to find delivery time.

I hope I could explain it well, thank you for your attention again.

[Marcol]

Provided that your Product Numbers are the same on both sheets and in the same order then there is no need for a macro.

In Sheet "MRP" D2

Please Login or Register  to view this content.

Fill/Drag Down

In Sheet "MRP" H2 (Format Column H custom > "mmmm-dd, yyyy")

Please Login or Register  to view this content.

Fill/Drag Down

The data can be extended in sheet "delivery" as far as you need.

See how this works and we can perhaps modify it a bit if required.

Hope this helps

[atkinsonmuldo]

Thank you for your help Marcol, but infact it is not what I mean, your formula give me the specific prodcut's last delivery date. But I need when my stock will compensate my delivery.

Maybe I did not explain my situation well, or may be I gave so detail. I just want to know; how can I use i or j parameter at below.

Sheets("delivery").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
col= Selection.Columns.Count

For i=1 to col

total=SUM(R[i]C[j]:R[i]C[j+1])

................

I want to show you just "total=SUM(R[i]C[j]:R[i]C[j+1])" the other codes are just an example. I just need to use these i or j parameter with sum but excel does not see my parameters cause of [ ] paranthesis.


And I have one more question to you, can we get the cell coordinates with macro. For example when my active cell is "B4" but I want to assign its coordinates to my parameters as "k" and "l" .So it will be Cell(k,l). (k=2;l=4)

Thank you again for your consideration