Find a specific string

Hi All
I am a beginner in VBA, I know little bit but I learn every day something new and that is very nice.
I am looking for help or code to solve that challenge.

On a regular basis I receive a very large Excel 2002 Sheet, with +/- 16 000 lines (yes +/-16 000 lines) with data coming from a main frame.
The data have to be re-organized in order to do a huge mail merge.
I have witten few macros that help me to do the portion of the job but the following step is more difficult for me.

I have to find strings like "CITY XX" or "SMALL TOWN IN CANADA XX", where XX is the provincil code, within 4 possible columns, say E, F, G, H. Range is E2 to H 16000.

Then, once found, these strings (City name and province code) must be copied on the same line but on column K, then deleted from its original location (E, F, G, H)

To be more precise, what I search is one or more words but with a Province Code, e.g. OTTAWA ON, VANCOUVER BC, CALGARY AB, CORNER BOOK MB, SAINT REMI DE LA LAUBINIERE QC, etc. The provincial code is important because ONLY this kind of strings must be copied on K regardless the number of words for the city/village name before and spaces.

Note the space between the name of the city and the Province code (2 letters only). Any space in the City name is not important.
In the columns E, F, G, H, there are other information but only the "CITY XX" format must be moved to K.
If the information contents numbers, it must be ignored, even if there is a "Province Code" after.

So the following must be ignored:

- TRANS CANADA HIGHWAY (there is not a space and a Province code)
- 123 Main NW (there is a number)
- Room 34 (there is a number)
- 34645654 SK (there is a number)
-GENERAL DELIVERY (there is not a space and a Province code)
- anything not formatted like "CITY XX" or "SMALL TOWN IN CANADA XX"

Hoping it is clear enough ...
Any advise, sample code, help would be very very appreciated.

Alain Kadlec
Ottawa.

AK69,

Welcome to the Excel Forum.


To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

AK69,

Thanks for the workbook.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the FindCityPC macro.

Hello Ak69,

Here is another version. This will validate each code before the data is copied over. This macro has been added to the attached workbook.

Please Login or Register  to view this content.

Hello AK69,

I have updated the macro to keep the addresses in the same row and if an invalid city code is present then the cell in "K" is left empty. Here is the macro which has been added to the attached workbook.

Please Login or Register  to view this content.

AK69,

With a different approach, and faster.

Detach/open workbook FindCityPCv2 w PC array - AK69 - EF773089 - V2 - VP - SDG13.xls and run macro FindCityPCv2.


If you want to try the macro on a different workbook (with your raw data on worksheet Sample Data):


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Then run the FindCityPCv2 macro.

AK69,


This updated macro, instead of looping thru each separate column it uses a rng, and, is faster because it only goes thru the PC array one time.


Detach/open workbook FindCityPCv3 w PC array VP rng - AK69 - EF773089 - SDG15.xls and run macro FindCityPCv3.


If you want to try the macro on a different workbook (with your raw data on worksheet Sample Data):


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Then run the FindCityPCv3 macro.