I have a bunch of contacts listed in column B which I´m transposing to a specific cells according to their data. I need to write VBA code that will always transpose the row that contains the CITY STATE ZIP CODE information to the corresponding cell.
This is part of my code that I have:
If Rng.Areas(Grp).Cells(Itm) Like "*[a-z],*[A-Z][A-Z]*#####*" Then ……
This works fine if the row contains: Lynchburg, VA 24503
But I also have a row that has: Karen C. Metz, NMLS #223664 and will accidentally place this data under the wrong cell value. It might also just have the City and State but no zip code: Lynchburg, VA
I think the key part is having the VBA code identify the two capital letters of the State within the string and then identify this row as the CITY value before transposing it to the appropriate column. I think this is the way to go because my data will always have the two letter state code.
I’ve attached my current code to see what I’m talking about, please help, I’m a newbie to VBA so my current code is not the best. Any suggestions or a better way to do this will be greatly appreciated.
Thanks
Bookmarks