• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtract yellow highlighted columns "diff" in reverse order

The number in the DIFF column is to be subtracted from H to C in such a way that the cell where the number is less than DIFF is zero, and the cell where the number is greater is subtracted from the remainder and the DIFF column is zeroed. There is nothing to do where there is no DIFF. Please see the attached file to know more. Data tables should look like this
 

Attachments

  • AGEING-EXCEL.xlsx
    14.3 KB · Views: 6
It looks like you want to subtract the values in reverse order, adjusting for the DIFF column. Below is a VBA code that you can use to achieve this:

Code: 
Sub ReverseSubtraction()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long, i As Long, j As Long
    Dim diffCol As Long

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the actual name of your sheet

    ' Find the last row and column in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Find the column index for the DIFF column
    diffCol = Application.Match("DIFF", ws.Rows(1), 0)

    ' Loop through each row in reverse order
    For i = lastRow To 2 Step -1
        ' Loop through each column from H to C in reverse order
        For j = lastCol - 1 To 8 Step -1
            ' Subtract the value in the DIFF column
            If ws.Cells(i, diffCol).Value > 0 Then
                ws.Cells(i, j).Value = WorksheetFunction.Max(ws.Cells(i, j).Value - ws.Cells(i, diffCol).Value, 0)
                ws.Cells(i, diffCol).Value = IIf(ws.Cells(i, j).Value = 0, 0, ws.Cells(i, diffCol).Value - ws.Cells(i, j).Value)
            End If
        Next j
    Next i
End Sub

Make sure to replace "Sheet1" with the actual name of your sheet. This VBA code performs the reverse subtraction as described in your example. It loops through each row and each column in reverse order, adjusting values based on the DIFF column.
 

pecoflyer​

Above Your given link isn't valid...

kjaypal2002​

As You know Forum Rules and which are for You too.
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
You must log in or register to reply here.
Back
Top