actually, it makes perfect sense.
"E" is a column that evaluates to a number (5)
both are single values.
"E2:E20" is a range.
the fact that they are all the same column is irrelevent.
If it COULD figure out you're wanting the Columns from the range,
"E2:E20" would return multiple values. (5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5)
they all happen to be the same,
but that too doesn't matter because the Cells() method
expects a SINGLE value.
looking closer, Cells(4,E2:E20)
shouldn't have compiled at all.
VBA should've interpreted E2:E20 as a variable and produced an error
because ":" is an illegal character in a variable name.
using Cells(4,"E2:E20") would have told the compiler that
E2:E20 is a string of characters.
There is nothing to indicate (to the compiler) that this string represents a
range.
You COULD use: Range("E2:E20").Column
to return a single value.
but keep in mind that something like Range("E2:Z200").Column
is still going to return (5) because
the .Column method is returning the first value of the "E2:Z200" array.
It's not going to magically iterate through all values in the range.
hope this helps you understand why "E2:E20" will not work in this case.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Michael Trombetta <mickey11030@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Mon, October 15, 2012 4:02:22 PM
Subject: Re: [ExcelVBA] Abbreviations to Words
Thanks, but I realize that Cells(4,5) refers to a particular cell. I just wonder
why Excel can understand Cells(4,"E") but not Cells(4, E2:E20), but I guess
that's just how it is.
________________________________
From: Ralph Gregory <ralph.gregory@skaino.co.uk>
To: ExcelVBA@yahoogroups.com
Sent: Monday, October 15, 2012 11:58 AM
Subject: Re: [ExcelVBA] Abbreviations to Words
Cells(4,5) references the 4th row and the 5th column which equates to E4 The
numbers can be substituted with variables if needed
Ralph Gregory
Contracts Manager
Skaino Services
West March
DAVENTRY
NN11 4SA
----- Reply message -----
From: "Michael Trombetta" <mickey11030@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Subject: [ExcelVBA] Abbreviations to Words
Date: Mon, Oct 15, 2012 4:14 pm
David,
I'm not trying to work on multiple cells; I thought Cells(4, E2:E7) was a way to
select E4, but I see that does not work.
Early in this thread, Derek posted the following
Cells(nRow, nFullNameColumn).Value = clxFruit.Item("key" &
nAbbreviatedNameColumn)
nRow is an index in a For loop, and I assumed nFullNameColumn was a variable
associated with a named range on the sheet. How does nFullNameColumn have to be
defined and assigned a value to make this statement work?
At the end of my last post I asked this question: With Fruit a defined name on a
sheet, and nF defined by Dim nF as Range, what value does the statement Set nF =
Range("Fruit") assign to nF? does anyone know the answer to that question?
Thanks for all the help,
Michael Trombetta
[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]
[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 (3) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar