AR Aging Cleanup via VBA

[ptmuldoon]

I am working to 'clean up' an AR aging that is being Exported from Microsoft Dynamics (formerly Great Plains) and can't seem to get this to work correctly.

The VBA is looking through the file to strip it down to a raw detail for easier summarization and pivot table use. And this will be done frequently on daily or monthly basis as needed.

I've attached a sample and the below code as well. If you run the macro, you'll see what I'm getting at. And this appears close I think, yet always gives the wrong totals. I think part of that is the document exporting the payment info into Column D but not all the time.

I just can't figure If I'm missing something in the macro, maybe a bad file?

Please Login or Register  to view this content.

Edit.
Forgot to mention, for some strange reason, you also get prompted about existing names existing, but there are no named ranges in the file?

[AlphaFrog]

It's not obvious to me what's wrong with the result from the example data provided.

I do see a potential problem with these two For-Next code blocks. If there are two adjacent rows that need to be deleted, the 2nd one will be jumped over and not deleted. Say rows 4 and 5 should be deleted. When the code deletes row 4, then row 5 shifts up to replace it. Then the code doesn't check row 4 again. It moves on and checks row 5 which is now the old row 6.

Please Login or Register  to view this content.

The solution is to loop through the rows from the bottom up to avoid missing a possible row.

Please Login or Register  to view this content.

[ptmuldoon]

Thank for trying to help, and I switched the code over to use yours by doing the cleanup from the bottom up.

But I'm still not quite right. I went and tried with a different months export and getting the same results. The 'cleanup' looks good, but I can not seem to get the correct totals.

In this one attached, the original file has a grand total of $4,244,319. When I do the clean up, I get a total of $4,401,059 when summarizing columns F through J. I know the 'Amount' column will definitely be incorrect as that is the original value. And the individual columns will be incorrect on a individual basis due to the payment rows not matching to their invoice.

But columns F through J should be getting me that correct grand total.

Run the macro on the Original WS. The Original (2) is just the current end result with totals showing the difference.

[AlphaFrog]

I don't know what to tell you. I couldn't even get the grand total $4,244,319 to equal the column sums on the original data. I used a SUMIF formula to sum each column excluding the "Totals:" rows e.g.;
=SUMIF($E$2:$E$3657,"<>Totals:",F2:F3657)
Then summed all those totals for F:J = $4,313,937.17

On the result data (Original 2), why do some PMT lines have the same values in E and F and others do not?