Excel 2010 Autofill via VBA - An Unconventional Way!

The attached file has the sample setup to illsutrate the problem.  There is also a large blue box on the tab explaining the goal.  I am not repeating the same description here as it would be unnecesssary duplication of effort but at the high level this is about having a cell which could predict what is being typed in by comparing to another range of data then autocompletes that entry in the cell.  The list that is provided on file displays the methods that can be used to achieve this goal but are not acceptable.  Therefore it warrants a custom VBA solution to achieve this goal.
Ray ErdenBusiness Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Try attached workbook.
The principle is datavalidation as you type.
When you select the green cell a selection box opens on top of the cell.
Start typing, and the box shows dynamically the first matching record of city names.
Press Enter to insert the value, or use the arrow to show the list starting with the city name shown.
Select on the list, press Ok button or Enter.
For another value, select another cell, and then the green cell.
Esc close the box without writing to the cell.

The function is designed to show the selection box on top of the selected cell, so the input range could be more cells.
The name of the input cell(s) is CityInput.

The city list is moved to sheet CityList.
When the sheet is activated or changes are made, adding names at the bottom or deleting names, the list is cleaned for blanks, and sorted.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray ErdenBusiness Systems AnalystAuthor Commented:
Thank you!  Worked out pretty good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.