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]

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (10)

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

.

__,_._,___