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.
Bookmarks