0 votes
in VBA by Beginner (2 points)
I have a financial journal with three basic columns.

`1 Expense/Income

2 Debit

3 Credit

The journal is populated from the accounting system and contains entries which are not known to the capturer and threfore puts all unknown entroies (debits and credits into this Journal.

Once the entry is resolved, say it was a credit then the account gets debited in this journal and credited in the correct one. Now we have the same value in debit and in credit. I would like to run a vb script to identify duplicates in the two columns and the delete both rows.

I have tried it but when I go into the first column Put the value into variable, go to the next column anf find the same value. If Found the row in the second column is deleted then return to the first column and delete that row. My problem was whenni got to a nill cell in the first column the script seized

 

Can you give me basic guid line

 

thank you

 

Hannes
by Skilled (419 points)
+1

Hi Hannes,

Since you have an almost-working script, can you paste it into this forum so that we can see what's going on? I have a few observations, from what you've written:

  • Since this involves deleting rows, are you working from the bottom row to the top? If your loop goes from 1 to 100, for example, if row 1 has a match with any later row, when the loop counter arrives at that row (later, of course), the row will be blank.
  • Even if you are shifting rows up to eliminate blank rows, each deletion means that you will have fewer than 100 rows to look at, so when the loop counter gets near the end, it will encounter the empty rows that used to be row 101, row 102, etc.
  • In accounting, what is the purpose of this deletion? Is this what is meant by account reconciliation? I'm asking because I was wondering if it would be simpler to have a 4th column with an "R" placed for each entry that had matching debit and credit (I vaguely remember this from my days of puttering around with GnuCash.)
Cheers,
 
Mitch
 

1 Answer

0 votes
by Beginner (12 points)

Hannes Beginner coloca o codigo para a gente ver se consegui ajudar

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...