Query US Abreviations

[IonutC]

Hi guys,

I have a question.

I have this code

Please Login or Register  to view this content.

How can I develop the code so I will not have all the time to fix range (E.g. in col.D).
The US abbreviations sometimes are in col.G, or other and I want to run my code, so it will populate in col.Z the full name of the Abreviations.

If I create a Range ("State") with Ctrl.Shift+F3 Excel will know that maybe the range will be in other column and i want based on the col. V(E.g.) to populate in Z the full name of the US state.
I don't want to use Range("D2:D200000"), or to edit the macro all the time, when I have a different column set into a spreadsheet. ..so I need to expand my code to auto detect the Range of the State, based on a variable called State...

Thank you in advance,
Ionut

[MarvinP]

Hi Ionut,

This problem sounds like you need one of a few other things.
1. If you create a Named Range of cells and put in all the names and ID's you might be able to use a VLookup to replace what you want. This might eliminate the need for VBA code.
2. It looks like you need a Dynamic Named Range so you can move your lookup table around. Then you can use the DNR's in your VBA Code.
3. If you can't have the table of Names and ID's live in cells on/in your workbook you can make them a Constant Array in the Names Manager and use it to do you lookups.

read:
http://www.bettersolutions.com/excel...G820716330.htm
http://www.databison.com/excel-array...ray-constants/

[IonutC]

Thank you Marvin I will read them to see if I will be able to rewrite the code....

Have a great day,
Ionut

[MarvinP]

Hi Ionut,

Here is a better answer using ActiveCell.Column . You simply click in the column you want converted and run the macro while your cursor is in that column. See code and attached:

Please Login or Register  to view this content.

[IonutC]

Thank you Marvin!