Need help with a workseet_change event macro

[Tony Valko]

Hi Folks!

Not much of a programmer.

I need some help on a worksheet_change event macro.

I want to type in the first few letters of a NFL city name and when I hit enter the macro will replace what I've typed with the full city name.

For example, if I type the letters pit then the macro will replace that with Pittsburgh. If I type dal then the macro will replace that with Dallas. If I type ne then the macro will replace that with New England.

I am aware that this could be done using AutoCorrect settings but that is a global change (even across Office applications) and I only want this to be a local change in a specific file on a specific sheet in a specific range.

I even tried this using drop down lists but that is a very tedious task and I think what I want to try with the macro will be much faster.

I need to do this for all 32 NFL cities but if you show me how to do the first few I should be able to add the others.

The cell range will be A4:C305

Here's the first few names...

ari = Arizona
atl = Atlanta
bal = Baltimore
buf = Buffalo

Appreciate any help you can offer.

[AlphaFrog]

Add your cities to the array in alphabetical order.

Please Login or Register  to view this content.

[cytop]

Another approach:

Please Login or Register  to view this content.

This uses a 2 column Named Range on another sheet. Abbreviations in Col 1, Full name in Col 2.

[jindon]

It is always better to handle multiple input at a time.

Please Login or Register  to view this content.

[Tony Valko]

Thanks to everyone for their efforts.

I will try each suggestion and let you know how I make out.

[Tony Valko]

Tried each of the suggestions and they all do the job.

Thanks to everyone for their help!



Don't pick the Steelers to win the Super Bowl!

[Tony Valko]

Add your cities to the array in alphabetical order.

arrCities = Array("Arizona", "Atlanta", "Baltimore", "Buffalo", _
"Charlotte", "Chicago", "Cincinnati", "Cleveland")

This worked so good that I regret not having asked for this years ago!

This is a true time saver and I can see benefits of using it in more applications.

Is there a limit to the number of items in the array? Can you make the array a named range on the worksheet? Would it be better to make the array a worksheet range of cells?

[AlphaFrog]

This worked so good that I regret not having asked for this years ago!

This is a true time saver and I can see benefits of using it in more applications.

Is there a limit to the number of items in the array? Can you make the array a named range on the worksheet? Would it be better to make the array a worksheet range of cells?

The array limit is memory dependent. In this case, it's not a factor.

You could read a range into an array (named or not).
Dim arrMyArray as Variant
arrMyArray = Range("B2:B100").Value
The result array has the same two dimensions as the range. In this example; (1 to 99 by 1 to 1)

[Tony Valko]

So then this line of code:

Please Login or Register  to view this content.

Would become?

Please Login or Register  to view this content.

[AlphaFrog]

Sorry no.

Something like this. Change the sheet name and range to suit.

Please Login or Register  to view this content.

[shg]

Tony,

There's an add-in that will do this automatically for cells with list validation tied to a range.

The add-in is at https://app.box.com/shared/ono8ni9xvr, there's a readme at
https://app.box.com/shared/4qxybepxxr, and an example workbook at https://app.box.com/shared/e8dbbple34

[Tony Valko]

Ok, I'll play with this over the weekend.

Thanks once again!