#### Jyotirmaya

##### Board Regular

- Joined
- Dec 2, 2015

- Messages
- 168

Code:

```
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
```

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.

I want the above macro to be tweaked, at first the value of I2 should be checked if the value of I2 is greater than I1 then no problem, simple formula will apply B= D-C, but if value of I2 is less than I1 or the value of I2 is 0, then it will check if there are some value in U1, if have some value then that amount will be adjusted accordingly and adjusted amount should show in T1.

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.