Removing bad payments from report

Biznez · Apr 21, 2015

Hi , i have payments in report that went nsf and i would like to remove those 2 rows. For example

Code:

A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00[/color]
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00-

The red are the rows i want removed using vba code
Thanks

SkipVought · Apr 21, 2015

Hi,

I could derive what I think is the logic based on your example, but it would be much more desirable if you were to explicitly state the logic clearly, concisely and completely.

Biznez · Apr 21, 2015

Hey Skip, ok so we are in sheet "Input"
Column A starting at A5 has the Value date (cust payment date), in column B starting at B5 is the payment type meaning cash or pre-auth etc...and in column C starting at C5 is the amount cust paid. Sometimes when a payment is made that payment goes nsf. These are the rows i want removed.

Code:

A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
3/31/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	NF     	    487.00
5/12/2008	CS     	    974.00-[/color]
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
[COLOR=#EF2929]09/01/11        PA          36.24- 
09/01/11        NF          36.24[/color]

so i want the rows in red removed because those are bad payments

Biznez · Apr 21, 2015

sorry, correct red row removed are these ones not the one above

Code:

A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00[/color]
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
[COLOR=#EF2929]09/01/11        PA          36.24- 
09/01/11        NF          36.24[/color]

Biznez · Apr 21, 2015

i think the best way to do this is if in column C starting at C5 finds the negative sign "-" and one cell below has no "-" sign, those 2 rows can be deleted.

SkipVought · Apr 21, 2015

I reall don't believe that you have stated the logic completely.

Think it through again and make a clear, concise and complete logic statement.

Biznez · Apr 21, 2015

ok let me try again. I already extracted data from mainframe and now we are working in excel sheet "Input" only.

If in column C there is a character "-" and one cell below has no character "-" then delete those 2 rows

SkipVought · Apr 21, 2015

So the date is irrelevant. It mattered not that the dates are identical or not?

BTW, this is a forum707 question.

So the first thing that you ought to have done is to change the TEXT on the amount column, that you could not possibly use in ang arithmetic operation, to NUMBERS.

You might use a formula like this...

=IF(RIGHT(C5,1)="-",-1,1)*LEFT(C5,LEN(C5)-1)

Biznez · Apr 21, 2015

how can i use this in vba code?

Biznez · Apr 21, 2015

Hi Skip, yes dates are always identical for bad payments but the problem there would be if the customer made 2 payments on the same day those will also get deleted. so thats find a character would work. Any suggestions?

SkipVought · Apr 21, 2015

What about that gormula?

SkipVought · Apr 21, 2015

Sorry formula.

Fat fingers on my iPhone!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Removing bad payments from report

Biznez

Technical User

SkipVought

Programmer

Biznez

Technical User

Biznez

Technical User

Biznez

Technical User

SkipVought

Programmer

Biznez

Technical User

SkipVought

Programmer

Biznez

Technical User

Biznez

Technical User

SkipVought

Programmer

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor