# 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

• 9.1 KB Views: 16

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