Macro which sorts and matches entries not working but not erroring

[Nick_G]

Hi all,

I've done some programming in Python and R but this is my first foray into VBA so my apologies if any of this turns out to be an absurdly stupid question!

I'm trying to write some VBA code which will do the following two tasks.

1: Sorts a range of data by Name and then by Absolute Value

2: Looks through the sorted data, and if two rows have the same Name and Values which sum to zero, deletes both rows.

As it stands, the code I've written works some of the time, but not all of the time. I've attached a spreadsheet with examples of where it works and where it fails. The code behaves as expected for "Anna" and "Tom" but fails for Jack. It should delete both entries as the names are the same and the values are the same.

Any advice would be greatly appreciated.

The code is as follows (a text box is assigned to the first piece of code (Sort_And_Match):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JLGWhiz]

The parameters for the Rng variable were set up incorrectly for proper use in the For Each statement.

Please Login or Register  to view this content.

Also, your 'Matching' procedure needed some work.

Please Login or Register  to view this content.

To troubleshoot a process using multiple macros, open the vb editor to the primary code module (starting macro) and then use the F8 key to step through the procedure and see where your problems are.

[Nick_G]

Firstly - thank you very much. That works perfectly and is exactly what I expected.

I have a couple of questions about the changes you made to the Matching procedure.

Please Login or Register  to view this content.

This starts at the end and works backwards until it stops at Row 3 (2 if you consider RowNumberPlus, which is really RowNumberMinus with working backwards)

Why do you do it this way?

Also why the manual update of

Please Login or Register  to view this content.

? Given

Please Login or Register  to view this content.

, wouldn't this happen automatically?

Once again, it works perfectly and I'm very grateful! Just trying to figure out "why"

[JLGWhiz]

When deleting rows, starting from the bottom assures that rows will not be skipped because the loop automatically increments to the next row on each iteration. Since Excel shifts the rows as rows are deleted, If the loop begins at the top, then the shifted row will be skipped because the automatic increment of the loop looks at the next row and ignores the row that was shifted into the deleted position. By starting at the bottom, that part of the problem goes away. The deleted row will be below the next increment.

The' i = i - 1 ' statement is to compensate for the two row deletion and give i a value so the autm increment will be for the correct row.

The Step -1 simply tells VBA how to increment the loop upward.
Regards, JLG

[Nick_G]

Many thanks! Will mark thread as solved and keep this in my pocket if I'm asked to dip my toes into VBA again.