• 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

chirayu

Well-Known Member
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
 

Marc L

Excel Ninja
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 !
 

Shabbo

Member
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.
 
Top