Re: [ExcelVBA] Abbreviations to Words

 

Dear Mickey

You have to get the collection items out one by one, you cannot use range methods.

So :-

Sub Main()
Dim clxFruit As New Collection
    clxFruit.Add Range("B2").Value, "key" & Range("A2").Value
    clxFruit.Add Range("B3"), "key" & Range("A3").Value
    clxFruit.Add Range("B4"), "key" & Range("A4").Value
    Debug.Print clxFruit.Count
    Cells(2, 3).Value = clxFruit.Item(("key" & Range("A2").Value))
    Cells(3, 3).Value = clxFruit.Item(("key" & Range("A3").Value))
    Cells(4, 3).Value = clxFruit.Item(("key" & Range("A4").Value))
' or what you really wanted was :-
Dim nRow As Long
    For nRow = 2 To 4
        Cells(nRow, "c").Value = clxFruit.Item(("key" & Cells(nRow, "a").Value))
    Next nRow
End Sub

By the way I use .Value just in case the default property is not .Value and don't bother with  Set shtSht1 = Sheets("Sheet1") if that is the active workbook. (sorry Yahoo format not working)

Don't use Integer as there is no such thing in Win32 .

Lastly . Do not abbreviate object names. There is no tax on keystrokes. - "Abb", shtSht1, nA  etc

Regards

Derek Turner
Office Automation Consultant
England
---

>________________________________
> From: dguillett1 <dguillett1@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Tuesday, 9 October 2012, 14:42
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
> 
>For .value=.value to work the ranges must be the same SIZE. Probably not tested.
>If all else fails, send a file to ME
>
>range("a2:c2").value=range("a2:d2").value will NOT work
>range("a2:c2").value=range("b2:d2").value will work
>
>Don Guillett
>Microsoft Excel Developer
>SalesAid Software
>dguillett1@gmail.com
>
>From: Michael Trombetta
>Sent: Monday, October 08, 2012 8:06 PM
>To: ExcelVBA@yahoogroups.com
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>Derek,
>
>Collections - again!
>
>I can't get your statement
>Cells(nRow, nFullNameColumn).Value = clxFruit.Item("key" & Cells(nRow, nAbbreviatedNameColumn).Value)
>
>to work.
>
>Here is my code
>
>Sub Main()
>
>Dim clxFruit As New Collection
>Dim shtSht1
>Dim nF
>Dim nA
>Dim nrow As Integer
>Set shtSht1 = Sheets("Sheet1")
>
>clxFruit.Add shtSht1.Range("B2"), "key" & shtSht1.Range("A2")
>clxFruit.Add shtSht1.Range("B3"), "key" & shtSht1.Range("A3")
>clxFruit.Add shtSht1.Range("B4"), "key" & shtSht1.Range("A4")
>Debug.Print clxFruit.Count
>Debug.Print clxFruit.Item(2)
>nF = Range("Fruit")
>nA = Range("Abb")
>nrow = 3
>Cells(nrow, nF).Value = clxFruit.Item("key" & Cells(nrow, nA).Value)
>
>A2 to A4 contain abbreviations, and B2 to B4 contain names. The 2 Debug.Print statements show that the assignments worked.
>Abb is the name I gave to a column of abbreviation, and Fruit the name of the cells which I want to contain the names. I think my last statement is an accurate implementation of your statement, but when I execute it I get a "Run-time error '13', Type mismatch.
>
>What did I do wrong?
>
>Thanks,
>Mickey
>
>[Non-text portions of this message have been removed]
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (11)

Recent Activity:

• New Members 3

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 • Send us Feedback

.

__,_._,___