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