Tewak in the VB code required to perform conditional tasks

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
I am using the following code to calculate the Balance from the data, Balance = Demand - collection

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
But I have some problems now, 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.

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.


 

Forum statistics

Threads
1,081,418
Messages
5,358,567
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top