Find Post code within row and move to specific cell within same row.

Hi,

Some of the data that I'm importing does not always come across in the correct format. Meaning that my data doesn't land int he right cells.

In this case I am trying to find references of post code SE9 across rows.

It will only ever appear once in a row.

Due to the inconsistent data entry on the imported list, the post code will appear in either column E, H and I.

I would like to have a macro that would search those columns for SE9, and if found, cut and past it to the same row in column F.

Is this possible? While doing searches I've been finding a lot of things on the Right, Left, Mid functions but don't think this applies.

In reality, I would need a macro that would check for multiple values across those columns (SE9, SE18, SE1, SW9, W1) and move them all to the same cell within their row.

Thanks in advance for anyone who can point me in the right direction.

Frank

excel test.jpg

Thanks for this Walruseggman,

This code works to find all of the values but once I run the macro they seem to disappear, not populate in a column. I still need columns E,H and I searched but need the info moved to column J in the same corresponding row.

I have no experience with VBA so I'm not exactly sure how to set the found items to be placed in col J from the code above.

Any help would be appreciated.

Thanks,

Frank

Hi Walruseggman,

While I was creating my example workbook I actually discovered what the code was doing. I've now set it up and tweaked it just a bit and its working great.

I do have a question though. Lets say I run the macro on columns B,C,D,E to find the post codes and set the destination column to 6 (col F) the numbers will populate.

However, if I try to set the destination column as one of the first columns I searched in (2,3,4,5) the info won't populate it will just disappear. I started this example on Col 2 so I could try to set the destination to Col 1 but still not results. Just wondering if this code only works from left to right.

Thanks for you help on this. This is going to save me hours and give me clean data!!

If you think you'd like to see an example work book for this then I can attach.

Thanks,

Frank

Hi,

Apologies for the late reply on this.

I have made a sample workbook.

Little bit of history (because I dont know if it will matter or not)

The original code you gave me works fine. If I put it into an empty workbook it works flawlessly every time. It falls apart if I try and implement it with other data on the page.

The original verson of the attached workbook actually has 12 active page tabs and as you can see from the macros section, has 5 or 6 other macros that i use.

I have taken your original code posted below and re-worked it to include the ranges and destination cells I currently require. To be sure, these are.

1. Search Col H,I,J,K,L for the following values
2. a = Array("SE3", "SE7", "SE8", "SE9", "SE10", "SE13", "SE14", "SE18")
3. If found, move those values to Col 13 (M) - In the same row that they were found in

One thing to note is that there are already some values in Col 13. I would like those values that are already there to remain unchanged.


After changing the code to suit my needs and running it on the "Output" page of the attached file I receive a whole host of errors (seemingly randomly). These include. Runtime 5 Error (Most Common), Excel Crashing and a Complie Error: "Next Without For".

The name of the macro currently being used to perform the function is move_postcode_2

Any help is much apperciated and thanks again for getting me this far.

Frank