Re: [ExcelVBA] Abbreviations to Words

 

Dear Michael

My comment referred to your code :-

nF = Range("Fruit")
nA = Range("Abb")
nrow = 3
Cells(nrow, nF).Value = clxFruit.Item("key" & Cells(nrow, nA).Value)

You say that "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"
This looks like you are attempting to fill the named range nF all at once from the named range nA. ? VBA does not support this.

You have to do the look-ups one by one, in a loop.

In :-

 Cells(2, 3).Value = clxFruit.Item(("key" & Range("A2").Value))

"A2" is one single value combining the row and column indices.
In the loop you want to increment the row index but keep the columns constant, so :-
Cells(nRow, "c").Value = clxFruit.Item(("key" & Cells(nRow, "a").Value))

Notice that the following expressions are equivalent :-
Debug.Print Cells(2, 1)
Debug.Print Cells(2, "a")
Debug.Print Range("a2")
Debug.Print Range("a" & 2))

The reason for using  Cells is that you can define rows and columns independently.

I hope this is more clear ?

Regards

Derek +++

>________________________________
> From: Michael Trombetta <mickey11030@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Wednesday, 10 October 2012, 22:12
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
> 
>Derek,
>
>I copied your code into my spreadsheet, and of course it worked, but I don't know why. I don't understand your comment. "You have to get the collection items out one by one, you cannot use range methods." But in the statement,
>
> Cells(2, 3).Value = clxFruit.Item(("key" & Range("A2").Value))
>
>you use Cells on the left side and Range on the right. Then, in an apparent change, in the For loop, you use
>
>Cells(nRow, "c").Value = clxFruit.Item(("key" & Cells(nRow, "a").Value))
>
>with Cells on both sides.
>
>What am I missing?
>
>Thanks,
>Michael Trombetta
>
>[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 (13)

Recent Activity:

• New Members 4

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

.

__,_._,___