Move Data From One Column to Another based on column headers and Row Text

I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)

I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.

I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".

Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.

If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.

The different scenarios are listed in my sample spreadsheet.

Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.

PO Box Address Test.xlsx

I am new to VBA so I would appreciate any help at all.

Maybe:

Please Login or Register  to view this content.

patel45 your code works for this sheet but, I do not believe your code is looking at the column headers. Those headers will not always be in those columns, I need it to look for "ADDRESS1", "ADDRESS2", and "ADDRESS3".

Try:

Please Login or Register  to view this content.

JOHN H. DAVIS
It gave me a run-time error '1004' on...
If Cells(i, zz).Value Like "*PO*" And Left(Cells(i, xx), 3) <> "c/o" Then

It only gave me that error when I moved the columns around beforehand. It worked perfectly on the sample spreadsheet.

I'm not duplicating the error when I test on your sample. Try to re-copy the code and repaste to your module and run it again.

Originally Posted by DerbysGal

It only gave me that error when I moved the columns around beforehand. It worked perfectly on the sample spreadsheet.

I retested it after repositioning the Columns, and I still cannot duplicate the error?

It looks like I only get the error when I place one of the address columns in column A or B. Besides that it is working great.

OK. The way it was written, that would cause an error. Here is a corrected version:

Please Login or Register  to view this content.

YOU ARE AMAZING! THIS IS PERFECT!
I wish I could something for you. Is there a place to rate you or sing your praises? Let me know and I will gladly do it.
I hope you have a great day and thanks again!

You're welcome. Glad to help out and thanks for the feedback. If you wish to acknowledge just click the Add Reputation star at the bottom of my post.

JOHN H. DAVIS
I have another problem. I did not take into account that there could be business names or sub-division names in “ADDRESS1” and/or “ADDRESS2” as well. They also did not tell me that there could be 4 address lines, not just three. This just keeps getting better.
Is there a way to only move the PO Box to the previous address line number if the previous address line starts with a number?
I have placed a couple more examples in the spreadsheet below for you to view. Hopefully that will clear up what I am trying to say.

PO Box Address Test V2.xlsx

Try:

Please Login or Register  to view this content.

This works great, but sometimes ADDRESS4 will not be present. When I deleted it I got an error again. Is it possible for it to look IF ADDRESS4 is there?

Try:

Please Login or Register  to view this content.

Is there anything you cannot do?! You are great, thank you!

I think I rated you correctly, let me know if I did not. You deserve a great rating. I appreciate your help.

You're welcome. Glad to help out and thanks for the feedback and the rep.