Re: [ExcelVBA] Abbreviations to Words

 

Dear Michael and Steve

Probably a VLookup on 100,000 rows would take rather a long time. Let us know how long if you do it this way.

The  VBA way to tackle this is to load the 30 dictionary items into a collection :-
Dim clxFruit as New Collection

within a loop 30 times :
clxFruit.Add vFruitName, "key" & vFruitAbbreviation

then loop 100,000  times with  :-
Cells(nRow, nFullNameColumn).Value = clxFruit.Item("key" & nAbbreviatedNameColumn)

"key"  prevents VBA getting confused (e.g.) with 1 and "1" 

Use error trapping for missing abbreviations and typos, do Trim just in case.

Be aware also of the collection-like Dictionary Object but you would need to create a reference to the Microsoft Scripting Runtime to use it.

Regards

Derek +++

>________________________________
> From: Steve Tindle <stindle@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Sunday, 23 September 2012, 18:42
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
> 
>Mickey,
>
>Without knowing more about your database it's hard to envision how this would work, but I would investigate adding a new column and add a v-lookup formula in each of the 100,000 rows. When you put in M8U, say in column B, column C would vLookup and convert to Apples. If you didn't want to see the abbreviations then hide column B.
>
>This method would save time in programming and you wouldn't have to run a macro every time an abbreviation is changed.
>
>Steve
>
>________________________________
>From: mickey11030 <mickey11030@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Sunday, September 23, 2012 11:02 AM
>Subject: [ExcelVBA] Abbreviations to Words
>
>
> 
>I have a spreadsheet with about 100,000 rows. One column contains abbreviations that I need to convert to words. Sample: A stands for watermelon, Z4 stands for orange, M8U stands for apples, etc. The abbreviations contain both letters and digits, and are between 1 and 4 characters long. There are about 30 abbreviations in all.
>
>I know I can use a For loop to examine each cell and a Select Case statement to convert the abbreviation in a cell to a word, but I wonder if there is a tricky technique that would do the conversion more efficiently.
>
>Thanks,
>Michael Trombetta
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (4)

Recent Activity:

• New Members 2

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___