×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Removing bad payments from report

Removing bad payments from report

Removing bad payments from report

(OP)
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-
3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00
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

RE: Removing bad payments from report

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.

RE: Removing bad payments from report

(OP)
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-
3/31/2008	NF     	    487.00
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
09/01/11        PA          36.24- 
09/01/11        NF          36.24 


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

RE: Removing bad payments from report

(OP)
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-
3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
09/01/11        PA          36.24- 
09/01/11        NF          36.24 

RE: Removing bad payments from report

(OP)
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.

RE: Removing bad payments from report

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.

RE: Removing bad payments from report

(OP)
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


RE: Removing bad payments from report

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

BTW, this is a forum707: VBA Visual Basic for Applications (Microsoft) 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)

RE: Removing bad payments from report

(OP)
how can i use this in vba code?

RE: Removing bad payments from report

(OP)
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?

RE: Removing bad payments from report

What about that gormula?

RE: Removing bad payments from report

Sorry formula.

Fat fingers on my iPhone!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close