can't find my mistake to calculate target price

[tommy1234]

Hello
I want to create an option to put a new target price (column E) to items / assemblies and due to what I insert the “new items price” columns will change (column F).
I wrote a code (module 3 in the attach file), but the problem is that my code can change the prices only for the items in its below levels - for example :
I insert the price ‘80’ in cell E8 so all the bellow level should be multiply in cell G8, but when there is a gap like in cells E15-E16 in which should be a different price cell E18 should be again multiply in G8, here my code fails to do that.
I hope a could explain what I meant , but if not
Column E - represent the target prices the user enters
Column F - is the result for the target price
Column H - is the final result

my boss pressures me to finish that until the end of the week - can someone help me.

thanks a lot

Avi

[SamT]

The following code is exactly the same as your code except:

I added the Enum to be able to replace all "Magic Numbers" in the code and make it more "transparent," or understandable.
I removed all unused and/or redundant code.
The lines of ************ highlight the error code.
This code replaces all the code in your attachment.

I must quit now. I may be back later.


Option Explicit

Enum ColumnHeads
Level = 1
Item_Code
Item_Price
Accumulated_Price
Target_Price
New_Items_price
Divider
New_Accumulated_Price
End Enum


Sub SamT_tagerprice_V1()

Const NumFormat As String = "0.00"
Const startrow As Long = 2

Dim Divisor As Double

Dim lastrow As Long
Dim rw As Long 'rw is nmemonic for Row. Rows.Count is greater than Integer limit.

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For rw = startrow To lastrow

If Cells(rw, Target_Price) <> "" Then
Divisor = Cells(rw, Target_Price) / Cells(rw, Accumulated_Price)
Cells(rw, New_Items_price) = Format(Cells(rw, Item_Price) * Divisor, NumFormat)
rw = rw + 1

Do Until Cells(rw, Target_Price).Value <> "" Or rw > lastrow
Cells(rw, New_Items_price) = Format(Cells(rw, Item_Price) * Divisor, NumFormat)
'************************************************
rw = rw + 1
If Cells(rw, Target_Price).Value <> "" Then Exit Do
'If Cells(rw, Target_Price).Offset(1) <> "" Then Exit Do
'************************************************
Loop

Else
Cells(rw, New_Items_price) = Format(Cells(rw, Item_Price), NumFormat)
End If
Next rw

End Sub

[SamT]

I ran out of time before.

Have you solved the issue yet?

[tommy1234]

thank for the replay, but unfortunately i couldn't find a solution to the problem.
i still can't get to the correct target price when i have more than one value.
i understand that i must do a loop that scan all levels (column A) and find the total target price and after that i should create 2 loops one inside the other. the first in until level in <= to the level of the first target price (target price should be : target price - total price) and the second scan to data to higher levels - 3,4,5 in the example) - i failed to the actually in the code (only in excel calculations)
do you have any idea how to solve that ? - column H is a good example to what the calculation steps should be.

[SamT]

You said: i still can't get to the correct target price when i have more than one value. One value of what? One value where?


You said: i must do a loop that scan all levels (column A) and find the total target price What? Do you mean the sum of all values in Column ("Target Price") where the level in Column "A" is identical?


You said: 2 loops one inside the other. the first in until level in <= to the level of the first target price That totally does not make sense in English. BTW, which is the first Target Price?


You said: target price should be : target price - total price That is a circular reference. Not allowed in calculations.


You said: column H is a good example to what the calculation steps should be. Column "H" is a list random =SUM's and values.

I am not trying to diminish you, I am trying to solve the language problem.