• 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.

Dr - Cr

Shabbo

Member
Dear Sir,

I wanted to minus cr amounts.
Example : wherever is mentioned cr should less from dr amount and show total as net after dr minus cr.
 

Attachments

  • debtor report.xlsx
    9.1 KB · Views: 16
Code:
Sub DR_CR()

Dim DR As Long
Dim CR As Long
Dim rng As Range
Dim subrng As Range
Dim lstrow As Integer

DR = 0
CR = 0
lstrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A4:A" & lstrow)

For Each subrng In rng
    If subrng.NumberFormat Like "*D*" Then DR = DR + subrng
    If subrng.NumberFormat Like "*C*" Then CR = CR + subrng
Next subrng

Range("C" & lstrow + 2) = DR - CR

End Sub
 
Hi !​
Another demonstration for the fun :​
Code:
Sub Demo1()
        Dim R&, T@
    With [A3].CurrentRegion.Rows
        For R = 2 To .Count
            T = T + .Cells(R, 1).Value2 * Sgn((.Cells(R, 1).NumberFormat Like "*Cr""") + 0.1)
        Next
           .Item(R + 1).Columns("A:B").Value2 = Array(T, "Net")
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Code:
Sub DR_CR()

Dim DR As Long
Dim CR As Long
Dim rng As Range
Dim subrng As Range
Dim lstrow As Integer

DR = 0
CR = 0
lstrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A4:A" & lstrow)

For Each subrng In rng
    If subrng.NumberFormat Like "*D*" Then DR = DR + subrng
    If subrng.NumberFormat Like "*C*" Then CR = CR + subrng
Next subrng

Range("C" & lstrow + 2) = DR - CR

End Sub
Dear Sir,
Formula working but its not suitable for large data, because my reqeuirement is to calculate customer wise and not total sum.
 
Back
Top