Simple VBA Macro code modification required...!!!

[Jyotirmaya]

I am using the following code to calculate the Balance from the data, Balance = Demand - collection

<code> 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</code>

there are some conditions to be applied while calculating the balance. The columns U W Y are the Excess amount columns and the value of those columns are depended for I N S columns Balance amount respectively.

For example in I1 demand is 50 collection is 10 so as per the formula, Balance = Demand - collection hence 50-10 = 40, but there are values in U column, I want that the rest 40 amount should be deducted from the U column & balance should show 0 and the adjusted 40 amount should show on T1 column.


Similarly N column is associated with W column and adjusted amount will show on V column & S column is associated with Y column and adjusted amount will show on X column.

With the above code
I have two problems
when U is 100
1. After running the macro in I3 Balance I am getting Rs 40, I want that zero & T1 is 40 great and U1 is 60 great.

I want the Balance I3 to be zero, because the Rs 40 amount has been adjusted with the value of U1. U W Ycolumn are the excess column, where consumer paid us in advance so that if he paid Rs 10 out of Rs 50 then we should have balance 40 but he has already deposited Rs 100 in advance for us thats why the Rs 40 need to be adjusted & should show on T1 & with the above code thats working but I need the I3 column to show zero.

2. when U 1 is having value less than balance
for example I1 Demand is 50 I2 collection is 10 & balance showed 40, but U1 having value 10 then that Rs 10 need to be adjusted U1 should be 0 but with your code I am getting U1 -30.

& I3 should be 50- collection 10 & adjusted 10 hence I 3 should be 30 & T1 should be 10

Same principle should apply for INS column associated with UWY column

Any help will be appreciated