Filter dr and cr balance

[sam51285]

Please help me to filter Dr and Cr Balance from attached sheet.


Sandy

[:) Sixthsense :)]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Function GetType(r As Range) As String
    GetType = Right(r.Cells(1).Text, 2)
End Function

In C2 Cell

=GetType(B2)

Drag it down...

[sam51285]

thanks and also an help to minus (-) sign before all Cr Balance if that possible

Sandy

[Richard Buttrey]

thanks and also an help to minus (-) sign before all Cr Balance if that possible

Sandy

Hi,

What was the decision process that formatted them with dr or cr in the first place? See post #3

[sam51285]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Function GetType(r As Range) As String
    GetType = Right(r.Cells(1).Text, 2)
End Function

In C2 Cell

=GetType(B2)

Drag it down...

thanks and also an help to minus (-) sign before all Cr Balance if that possible

Sandy

[:) Sixthsense :)]

In C2 Cell

=IF(GetType(B2)="DR",B2,0-B2)

Drag it down...

Or

=IF(GetType(B2)="DR","DR","-CR")

[sam51285]

In C2 Cell

=IF(GetType(B2)="DR",B2,0-B2)

Drag it down...

Or

=IF(GetType(B2)="DR","DR","-CR")

Thanks sir....

Sandy

[Richard Buttrey]

Hi,

What process has caused each cell to be formatted differently as either a Dr or Cr?
It seems to me that you'd be better advised to address that process and ensure that it produces numbers with the correct sign. Your data would be much more useful.