Excel VBA编程求助：实现Segments Left列的递减计算逻辑

Let's walk through what's wrong with your current code and how to fix it to match your desired outcome.

What's Wrong with the Existing Code #

Your current code only checks the current row's Segments Sold (column D) value and either sets Segments Left (column E) to 100 or subtracts the current D value directly from the initial 100. It doesn't track the running remaining value between rows, which is why you're getting incorrect results.

Corrected VBA Code #

Here's the revised code that implements your exact requirement:

Sub CalculateSegmentsLeft()
    Dim ws2 As Worksheet
    Dim LR1 As Long
    Dim k As Long
    Dim currentRemaining As Double ' Tracks the running Segments Left value
    
    ' Set your worksheet (adjust the sheet name if needed)
    Set ws2 = ThisWorkbook.Worksheets("YourSheetName")
    ' Get the last row with data in column B (or adjust to your relevant column)
    LR1 = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row
    
    ' Initialize with the fixed starting value from Cells(4,3)
    currentRemaining = ws2.Cells(4, 3).Value
    
    ' Loop from row 10 to the last row
    For k = 10 To LR1
        ' Set current row's Segments Left to the running remaining value
        ws2.Cells(k, "E").Value = currentRemaining
        
        ' If Segments Sold is positive, subtract it from the running total
        If ws2.Cells(k, "D").Value > 0 Then
            currentRemaining = currentRemaining - ws2.Cells(k, "D").Value
        End If
    Next k
End Sub

How This Code Works #

• Initialization: We start by setting currentRemaining to the fixed value from Cells(4,3) (100 in your case).
• Row Loop:
  1. For each row starting at row 10, we first set column E to the current currentRemaining value. This ensures all rows before the first positive D value show 100, and subsequent rows show the updated remaining amount.
  2. If the current row's column D has a positive value, we subtract that value from currentRemaining to update the running total for future rows.

This matches your desired logic perfectly:

• Rows 10 to the first row with D>0 will have E=100
• The row with D>0 will still show E=100 (since we set E first before updating the remaining value)
• All rows after that will show the updated remaining value until the next positive D value, where we repeat the subtraction.

Notes #

• Make sure to replace "YourSheetName" with the actual name of your worksheet.
• If your Segments values are integers, you can change Double to Long for better performance.

内容的提问来源于stack exchange，提问作者Infernez