Excel Macros

[dtherrian]

Hi!


Again i love all of your tips. I need to be pointed in the right direction and maybe given some pointers. I have an excel spreadsheet that i work with on a regular basis. What happens now is that i have another sheet that i have to look up information and fill the information in order to complete a mail merge with ms word. Here is an example of what i need to happen in the spreadsheet. If the state field is equal to AZ. I want it to automatically add Jeff. If it is equal to AK i want it to add Eric. However that is only the 1st part. We do have a few that if the state is CA and the zip code begins with 940-949 it should be Elizabeth, but if the state is CA and the zip code begins with 931-939 it should equal Craig.


What direction can you point me in? Do i need to write a macro is is the going to be visual basic?


As a secondary question does anyone know if there is a macro that would clean up a address field. ie. the address field say 123 Any Street, Suite 100 and i need it to say 123 Any St Ste 100?


Thank you in advance

Dawn

 

[Luke M]

You don't "have" to use macros, but it might be the cleanest. For the info your are describing, it could be done via formulas. It would just get bulky pretty fast.


If using macros, yes, you'll have to write it in Visual Basic for Applications (VBA). Assuming you are familiar somewhat with macros, you could write a

Loop that goes through each cell and checks the State. Then, using a Select

statement, you can determine what to do for specific states (and even extra steps for additional checks, like your CA example).


For the secondary question, there may be example already written on the internet, but the crux of it would be to do lots of Find & Replace checks on the range of cells. As you might imagine, this could get quite extensive depending on how many different combinations of words/abbreviations you have.