多重条件VB宏在Excel中，简单的代码需要修改

Question

我使用下面的代码来计算的数据平衡，平衡=需求 - 收集

代码：

Sub DCB() 
Dim c As Range 
Dim lRow As Long 
lRow = 1 
Dim lRowLast As Long 
Dim lRowDiff As Long 
Dim lRowPortion As Long 
lRowPortion = 1 
Dim bFoundCollection As Boolean 
With ActiveSheet 
    lRowLast = .Cells(.Rows.Count, 1).End(xlUp).Row 
    Do 
    Set c = .Range("A" & lRow) 
    If c.Value Like "*COLLECTION*" Then 
    bFoundCollection = True 
    ElseIf bFoundCollection Then 
    bFoundCollection = False 
    If c.Value <> "BALANCE" Then 
    c.EntireRow.Insert 
    lRowLast = lRowLast + 1 
    Set c = c.Offset(-1, 0) 
    c.Value = "BALANCE" 
    End If 
    If c.Value = "BALANCE" Then 
    .Range(c, c.Offset(0, 18)).Font.Color = RGB(0, 0, 0) 
    .Range(c, c.Offset(0, 18)).Interior.Color = RGB(200, 200, 200) 
    lRowDiff = c.Row - lRowPortion 
    .Range(c.Offset(0, 3), c.Offset(0, 18)).FormulaR1C1 = _ 
     "=SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*DEMAND*"", R[-" & lRowDiff & "]C:RC)" & _ 
     "-SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*COLLECTION*"", R[-" & lRowDiff & "]C:RC)" 
    lRowPortion = c.Row + 1 
    End If 
    End If 
    lRow = lRow + 1 
    Loop While lRow <= lRowLast + 1 
End With 
End Sub 

但我有一些现在的问题，在计算余额时有一些条件需要应用。 U W Y列是超额金额列，这些列的值分别取决于I N S列余额金额。

例如在I1中，需求是50个集合是10，所以按照公式，Balance = Demand - collection因此50-10 = 40，但是U列中有值，我想剩下的40个数量应该被扣除从U列&余额应显示0和调整后的40应该显示在T1列。

我想要对上面的宏进行调整，首先应该检查I2的值，如果I2的值大于I1则无问题，简单的公式将应用B = DC，但如果I2的值较小比I1或I2的值为0，则会检查U1中是否有值，如果有一些值，那么将相应地调整该量，并在T1中显示调整量。

同样N列与W列关联，调整量显示在V列& S列与Y列关联，调整量显示在X列上。

Answer 1

我想这是你想要什么：

Sub DCB() 
Dim c As Range 
Dim lRow As Long 
lRow = 1 
Dim lRowLast As Long 
Dim lRowDiff As Long 
Dim lRowPortion As Long 
lRowPortion = 1 
Dim bFoundCollection As Boolean 
Dim lRowLastDemand As Long 


With ActiveSheet 
    lRowLast = .Cells(.Rows.Count, 1).End(xlUp).row 
    Do 
    Set c = .Range("A" & lRow) 
    If c.Value Like "*COLLECTION*" Then 
    bFoundCollection = True 
    ElseIf c.Value Like "*DEMAND*" Then 
    lRowLastDemand = lRow 
    ElseIf bFoundCollection Then 
    bFoundCollection = False 
    If c.Value <> "BALANCE" Then 
    c.EntireRow.Insert 
    lRowLast = lRowLast + 1 
    Set c = c.Offset(-1, 0) 
    c.Value = "BALANCE" 
    End If 
    If c.Value = "BALANCE" Then 
    .Range(c, c.Offset(0, 18)).Font.Color = RGB(0, 0, 0) 
    .Range(c, c.Offset(0, 18)).Interior.Color = RGB(200, 200, 200) 
    lRowDiff = c.row - lRowPortion 
    .Range(c.Offset(0, 3), c.Offset(0, 18)).FormulaR1C1 = _ 
     "=SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*DEMAND*"", R[-" & lRowDiff & "]C:RC)" & _ 
     "-SUMIF(R[-" & lRowDiff & "]C1:RC1, ""*COLLECTION*"", R[-" & lRowDiff & "]C:RC)" 
     If ((.Cells(lRow, 9)) > 0) And (.Cells(lRowLastDemand, 21) > 0) Then 'If Column I Balance > 0 and U has value 
     .Cells(lRowLastDemand, 20) = .Cells(lRow, 9) 'T1 = Balance 
     .Cells(lRowLastDemand, 21) = .Cells(lRowLastDemand, 21) - .Cells(lRow, 9) 'Adjust Col U 
     End If 
     If ((.Cells(lRow, 14)) > 0) And (.Cells(lRowLastDemand, 23) > 0) Then 'If Column N Balance > 0 and W has value 
     .Cells(lRowLastDemand, 22) = .Cells(lRow, 14) 'V1 = Balance 
     .Cells(lRowLastDemand, 23) = .Cells(lRowLastDemand, 23) - .Cells(lRow, 14) 'Adjust Col W 
     End If 
     If ((.Cells(lRow, 19)) > 0 And (.Cells(lRowLastDemand, 25) > 0)) Then 'If Column S Balance > 0 and Y has value 
     .Cells(lRowLastDemand, 24) = .Cells(lRow, 19) 'X1 = Balance 
     .Cells(lRowLastDemand, 25) = .Cells(lRowLastDemand, 25) - .Cells(lRow, 19) 'Adjust Col Y 
     End If 
    lRowPortion = c.row + 1 
    End If 
    End If 
    lRow = lRow + 1 
    Loop While lRow <= lRowLast + 1 
End With 
End Sub