VBA Macro to process duplicate phone numbers in 3 worksheets

Hello Excel Forum Gurus,

I am attempting to make my first VBA macro to make life simpler when I get phone number updates from Verizon.

I have a list of the updated phone numbers, including whether the number is disconnected, or newly activated in a sheet called "VerizonUpdates"

I also have two blank sheets to process these entries into, "Processed" and "NotFound".

There are 4 possible outcomes for my information based on the two conditions:
(1) whether the phone number has already been processed and
(2) if it is a disconnect or newly activated line.

I want to take the first row of "VerizonUpdates" and get the phone number.
See if that phone number (column 11) matches any entries in "Processed"
If there is a match in processed, and it is a new activated number (signified by an "I" in column 6 in "VerizonUpdates)......overwrite the entire row in "Processed" where the match was found.
If there is a match in "Processed, and it is a disconnect number (signified by an "O" in column 6 of "VerizonUpdates")....delete the entire row in "Processed" where the match was found.

If there is NOT a match in "Processed" for the phone number, and it is a newly activated number.....I want to insert that row from "VerizonUpdates" to the top of the "Processed" sheet, pushing all its current entries down
If there is NOT a match in "Processed" for the phone number, and it is a disconnect number....I want to move the "VerizonUpdates" row to a new line on the "NotFound" sheet, signifying that the number was removed, but never on my Processed list


Here is my code so far: I am having particular difficulty with the foundRow, signifying the row where the match of the phone number from the Updates occured on the Processed sheet

Please Login or Register  to view this content.

Thank you for your help,
Adam

Please see a copy of the above with working data in my attachment or at http://ifile.it/10e32ok/VerizonUpdatesMacro_01.xlsm

I think that my answer lies in transferring Excel functions like Match Index Indirect Address into VBA functions like range. And also exploring the properties of the VBA objects.

your sample sheet includes zero data on the PROCESSED sheet, so a worthwhile test is impractical. Isn't the main point of this macro to update existing rows in the PROCESSED sheet?

Please update your sample to a better example.

You might also consider adding AFTER versions of the PROCESSED and NOT FOUND sheets so it's clear what you would expect done based on the sample data. You might find it easier to create a fully demonstrative sample workbook by reducing the VERIZONUPDATES sheet to about 25 rows.

I am working on cleaning up my code. I know there are a ton more clever ways to accomplish this result.

Please Login or Register  to view this content.

The thing I have the biggest problem with is the search to find the phone number in another tab.

This is how I would approach that:

Please Login or Register  to view this content.

All variables that are dimmed as RANGE are just that, a range of cells, from one cell to a full array.

I tend to name my variables in a way that indicate what they are. PhNum is the only one a little oblique, but it truly does mean "Phone Number". Basically it means "For each Phone Number currently stored in the range of cells defined as PhRNG"...

Check out the code above again, I added comments to clarify what each line of code does, though you may still have to examine carefully to see how it's doing that exact thing.

JBeaucaire,

Your comments cleared things up nicely.

I am still curious about the assumed states of variables of PhNum and RwFND if match is not found.

Why didn't you initialize PhNum, before sending it to loop with something like

Please Login or Register  to view this content.

? What value with PhNum start out at if it is not initialized like this?

Also, am I alright to assume if the Cells.Find command doesn't find anything, that the RwFND will be clear of all objects and return Nothing to this variable?

I was wondering why the

Please Login or Register  to view this content.

was outside of the loop, rather than directly above the find statement that could possibly cause the error. Is On Error a global variable that works within the whole Sub() if an Error occurs?

My next post will include my final solution (thanks to your help!), and revelations I learned along the way.


You are very kind,
Adam

Originally Posted by delforum

Why didn't you initialize PhNum, before sending it to loop with something like

Please Login or Register  to view this content.

? What value with PhNum start out at if it is not initialized like this?

PhNum is a Range. In the context of the FOR/NEXT, it means "take each cell in the PhRNG and process them one at a time". So the first time through the loop PhNum is given the value of B2 since that's the first "cell" in the PhRNG. The next time through the loop it is automatically given the value of B3. The FOR/NEXT cycles through the cells in order automatically. That's what it does. That's why we used it.

Also, am I alright to assume if the Cells.Find command doesn't find anything, that the RwFND will be clear of all objects and return Nothing to this variable?

Correct. In the context of the .FIND, the search for PhNum automatically inserts the value from the PhNum (which is actually a cell - B2, B3, etc.).

On Error holds its state until you change it to something else. It is often used directly above a single troublesome line of code, then changed to something else after it. If turned it on and left it on because the error code is occurring repeatedly inside the loop. No need to keep turning it on/off.

I was wondering why the On Error Resume Next was outside of the loop...Is On Error a global variable that works within the whole Sub() if an Error occurs?

Correct. It's above the loop so that anytime an error occurs in the .FIND method the code will continue to the "IS NOTHING" tests and proceed. Mostly that will result in items being added to the other sheets with nothing deleted.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.