VBA code to search column for string of 50 different cities......

[mahoney403]

Hi,

I need a VBA code that will insert a column nxt to A, search column A for a string of say 50 cities, and then enter the classification of that city into column B. example below.

Before
A B
1 Boston Good
2 Atlanta Good
3 Georgia Bad
4 NewYork Bad

After
A B C
1 Boston MA Good
2 Atlanta GA Good
3 Cleveland OH Bad
4 NewYork NY Bad

I'm not actually using ciies and states, but if someone could get me started on this code, I can play around with it to get what I want.

Thanks in advance

 

[Joe4]

I don't think you need VBA to do this at all. I assume that you have some "list" somewhere where you would be looking up each city to see what state it is in (or whatever values you are really looking up). In that case, you can simply use the VLOOKUP function to return the states.

If you look at Excel's built-in help files on the VLOOKUP function, it will show details and examples.

 

[mahoney403]

Thanks for the suggestion, but I pull a report daily that doesn't have the list, it's not 1 file, and I'd rather not take up the space with vlookp function but i appreciate the response.

 

[Joe4]

If there is no list, where do you expect these values to come from?
Regardless of how you do it (by formula or VBA), something is going to have to lookup the values you want somewhere, right?

If you have some "master list", I would look at making a macro that copies that master list onto a new worksheet in each file, and then uses the VLOOKUP formula. That can be automated.

 

[mahoney403]

"Regardless of how you do it (by formula or <ACRONYM title="visual basic for applications">VBA</ACRONYM>), something is going to have to lookup the values you want somewhere, right?"

Right, I would of rather'd the list be in the macro that would be stored on my personal excel file and not on the workbook I'm working in, thus saving space. Like I said I'm using cities as an example, I have more than 50 values and would rather not have a few hundred vlookup functions in my workbook, esp since other people use it. I guess I could try a macro with your suggestion then copy and paste the values.

 

[Joe4]

If you want a macro, you know that the macro code has to be attached to an Excel workbook, right? So you could have this universal list on a worksheet in that workbook.
Either way, that list is in the file, and VLOOKUP is much more easier than trying to store the list in some sort of array in VBA (and easier to maintain too).

You can still accomplish what you are after like this:
1. Open your new workbook and insert a new temporary sheet
2. Copy the lookup list to the new sheet
3. Insert VLOOKUP functions to lookup all the values you want
4. Do a Copy/Paste Special to turn those VLOOKUP values into hard-coded values
5. Delete the temporary lookup sheet

If you want, you could even program you VBA code to post the value of the VLOOKUP function in each instead of the VLOOKUP function itself (a little trickier, and not as clean looking in VBA, but shouldn't be too bad).

One of the advantages to storing the list on a sheet instead of in VBA (especially if others are going to be using it), if anyone needs to edit the list, they can do that without them going in to your VBA code and screwing that up (you want to keep all your users out of your VBA code!).

 

[mahoney403]

Thanks for your sugestions, I'll try them out.

"If you want a macro, you know that the macro code has to be attached to an Excel workbook, right?"

I have all my macros attached to my "Personal" excel file so whenever I open Excel they are all there, but none of them are actually on the files that I use them for, which are saved online for everyone to use.