VBA Subtract until a certain value with condition

[BalloutMoe]

Hello All, looking for some guidance on this problem I am stuck on. I am sure it is doable just can't figure it out at the moment and my vba skills are not the best. Below is a table and I am looking to subtract until amount is zero with a condition to not go under $25.50 in Column B.
So starting with 800 in Range("D2") I would like to subtract full amount from column A as you can see until it reached Range("A9") if the remaining balance is smaller then cell value in range A I would like to keep 25.50 and continue on to the next row until the 800 balance is zero.

Any help would be appreciated

ColumnA	ColumnB	ColumnC
$177.57​	$0.00​	$622.43	$800
$145.00​	$0.00​	$477.43
$99.63​	$0.00​	$377.80
$98.50​	$0.00​	$279.30
$90.32​	$0.00​	$188.98
$88.79​	$0.00​	$100.19
$64.97​	$0.00​	$35.22
$48.74​	$25.50​	$11.98
$47.12​	$35.14​	$0.00
$46.26​	46.26
$39.00​	39
$39.00​	39
$39.00​	39
$39.00​	39
$39.00​	39
$24.00​	24
$24.00​	24
$24.00​	24
$20.00​	20
$18.39​	18.39
$7.00​	7

 

[BalloutMoe]

In vba please and not a cell formula thank you

 

[Peter_SSs]

In vba please and not a cell formula thank you

Hmm, I wonder if that invalidates my approach?

Sub SubtractUntil()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=LET(x,SUM(A$1:A2)-SUM(D$1:D1)-G$2,IF(x<0,0,MIN(MAX(x,25.5),A2)))"
    .Value = .Value
  End With
End Sub

 

[BalloutMoe]

Hmm, I wonder if that invalidates my approach?

Sub SubtractUntil()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=LET(x,SUM(A$1:A2)-SUM(D$1:D1)-G$2,IF(x<0,0,MIN(MAX(x,25.5),A2)))"
    .Value = .Value
  End With
End Sub

Thank you for taking the time to respond. I will give it a go, I was hoping for a solution without a formula. Thank you

 

[Peter_SSs]

I was hoping for a solution without a formula.

Is there a particular reason for that?

 

[BalloutMoe]

Is there a particular reason for that?

Column b is usually not on the sheet but I guess I can use a helper column

 

[Peter_SSs]

Column b is usually not on the sheet but I guess I can use a helper column

I thought that it was column B that you are trying to produce. If that is not the case, what is/are the result/s that you are looking for and where would it/they go?

 

[BalloutMoe]

I thought that it was column B that you are trying to produce. If that is not the case, what is/are the result/s that you are looking for and where would it/they go?

Sorry for the confusion I want the results to be in column A to replace the current value if it was subtracted. I was just showing what I was hoping the results to be

 

[BalloutMoe]

I thought that it was column B that you are trying to produce. If that is not the case, what is/are the result/s that you are looking for and where would it/they go?

So to clarify I would like to Subtract Until 800 is zero from Column A only and replacing the value with zero if it was fully subtracted. and keeping 25.50 as minimum if Column A was larger then the remaining

 

[BalloutMoe]

I figured it out this is working so far. If anybody has any improvements.

lastR = Sheet3.Range("B" & Sheet3.Rows.Count).End(xlUp).Row
initialSubtracted = Sheet3.Range("A3").Value
Do Until initialSubtracted = 0
For i = 2 To lastR
        
        If Sheet3.Range("D" & i).Value <= initialSubtracted Then
        initialSubtracted = initialSubtracted - Sheet3.Range("D" & i).Value
        Sheet3.Range("D" & i).Value = ""
        Debug.Print initialSubtracted
        
        ElseIf Sheet3.Range("D" & i).Value > initialSubtracted And Sheet3.Range("D" & i).Value - initialSubtracted < 25.5 Then
        InvoiceRemaining = Sheet3.Range("D" & i).Value - 25.5
        initialSubtracted = initialSubtracted - InvoiceRemaining
        Sheet3.Range("D" & i).Value = "25.50"
        Debug.Print initialSubtracted
        
        ElseIf Sheet3.Range("D" & i).Value > initialSubtracted And Sheet3.Range("D" & i).Value - initialSubtracted > 25.5 Then
        Sheet3.Range("D" & i).Value = Sheet3.Range("D" & i).Value - initialSubtracted
        initialSubtracted = initialSubtracted - initialSubtracted
        Debug.Print initialSubtracted
        End If
If initialSubtracted = 0 Then Exit Do
Next
Loop