Calculate BOM levels with target price

[tommy1234]

Hello
i'm trying to create a macro (preferred) or a formula that will calculate my BOM levels automatically.
I have a BOM with 5 levels (can be more) with prices for each item and total price that is accumulated price refer to the BOM levels.
When i addend a new columns named "Target price" i wanted to define my price and update the BOM levels that are below and above with the new price.
columns H, I and J are examples for the calculation should be used and column K is the "new Bom Price" (the percentage on the item by its level multiplied with the accumulated price) according to the percentages in the BOM.

Thanks

Avi

[tommy1234]

CAN SOMEONE HELP ME ?

[p45cal]

This seems to work with your example sheet:

Sub blah()
For Each cll In Range("A2:A20")
  Set celle = cll.Offset(1)
  x = cll.Offset(, 2).Value
  Level = CLng(Replace(cll.Value, ".", ""))
  Do Until CLng("0" & Replace(celle.Value, ".", "")) <= Level
    x = x + celle.Offset(, 2).Value
    Set celle = celle.Offset(1)
  Loop
  cll.Offset(, 3) = x
Next cll
End Sub

[tommy1234]

Thanks for the answer, but the answer doesn't reffer to the target price - the target price (in column G) should be a number that the user will enter and all the levels below that will get a new price according to their percentege in the BOM (there is an example in the attachment).
its urgent
thanks


Avi

[snb]

What is BOM ??

I don't like shouting posters.

[tommy1234]

BOM = Bill Of Materialthis is an hierarchy list of all the items in which a product tree in build from.
each "father" has many or one child (2 levels) and the childs have more levels below them
the excel which i attached shows the process need to be done and the calculations


Avi

[p45cal]

I think this is very easy.
Take your target price of 280 and your actual price of 229.19 in cells G8 and F8 respectively and use them in the forumula:
=G8/F8
Let's put that in cell (say) O7.
Then in cell L8 have the following formula:
=C8*$O$7
paying attention to the $ symbols.
Format the cell as you wish (your others were numeric to 1 decimal place) and copy down to L20.
The values in the cells L8:L20 exactly match your example range K8:K20 but calculated a lot more simply.