One more follow up. I closed down Excel completely, restarted it again and
everything is working as I posted it again. Hmm! It seems to work on a newly
started session of Excel and then, as things happen during the session
(although I am not sure what those things are<g), it ceases to work any
more until a new session of Excel is started again.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Okay, this is strange. Everything I posted, which worked fine as I posted
it, has now stopped working! All I did was unselect the selection I had,
went to Help/About again, came back and made a different discontiguous
selection and now

Selection.Value = 2 * Selection.Value

generates a "Type Mismatch" error. Same for Range with the discontiguous
text string reference. I have no answer as to why, but it seems you were
correct.... in the general case, you cannot rely upon what posted (which
**was** working for me just a short time ago). Weird!

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm not sure what to tell you, but I tried what I posted before posting
it (and again just now to confirm it) and what I posted works as I
described it on my system, which the Help/About menu item says is
Microsoft Office Excel 2003 (11.8211.8202) SP3.

What I did is layout a large block of cells with 1's in them and then
(for the selection method) I randomly selected discontiguous blocks and
single cells with the Control Key down to make a discontiguous overall
selection. Next, I right-clicked the worksheet tab and picked View Code
in order to make the worksheet the active code window in the VBA editor.
And then I typed...

Selection.Value = 2 * Selection.Value

in the Immediate Window and hit the Enter Key. All the values in the
discontiguous Selection were multiplied by 2. Next I typed

Selection.Value = "AB" & Selection.Value & "CD"

into the Immediate Window and hit the Enter Key. All the values in the
discontiguous Selection that were previously multiplied by 2 now had "AB"
concatenated on the front of them and "CD" onto their backs. Then I
repeated the exact same steps, but used a text string value as the
argument for the Range collection object and the same things happened for
the specified discontiguous cells that the text string represented.

Are you saying none of the above happens for you when you try what I have
outlined?

Rick


"Peter T" <peter_t@discussions wrote in message
...
I'm not sure about all that Rick.

2 * Selection.Value
that would only work if the selection is a single cell, although the
single
result could be assigned to the value of a multi-area

arr = Range("A1:C4, J4, L6:L9").Value
In this 'arr' would only size to the first area, 4x3. Thereafter cannot
do
2 * arr

Generally discontiguous ranges need to be handled separately, eg

For Each rngArea in multiRange.Areas

Copy/paste(special) barely works with multiple areas, although it just
might
if the areas are same size or, say multiple areas of different heights
but
same width and in same columns.

Regards,
Peter T


"Rick Rothstein (MVP - VB)" wrote
in
message ...
The cells you wish to multiply by 2 (or do anything else with, within
reason) do not have to be in a contiguous block of cells. Select a
bunch
of
cells with numbers in them on a worksheet (make sure you use the Ctrl
key
to
select them so that you have some contiguous cells and some isolated
cells
in the selection), then right-click the tab for that worksheet and
select
View Code (this will take you to the VBA editor and make sure the
active
code window is set for the worksheet where your selections are). Now,
execute this statement in the Immediate window...

Selection.Value = 2 * Selection.Value

Now go back to the worksheet and you will each selected cell's value is
twice as big as it was before. You, of course, do not have to use the
Selection to do this; you can specify a discontiguous range directly in
code
and to the same operation...

Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value

And, of course, multiplication is not all you can do...

Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4,
L6:L9").Value

where I have concatenated "XX" onto the front of whatever is in each
cell
in
the discontiguous range "A1:C4, J4, L6:L9".

Rick


"LunaMoon" wrote in message
...
Hi all,

I am learning Excel/VBA via using it in real day-to-day work.

Two questions:

1. How to multiple a whole range of cells by 2 all together and all
at
once? (element-wise).

Of course, I am looking for more flexibility, such as apply a
function
to the whole region of cells.

I understand that it could be done for rectangular shapes; is there a
way to do this by first select a bunch of cells and label the cells
as
one single region(non-rectangular and non-regular shaped), and then
apply function to this region as a whole?

2. This time I have a function in XLL (treated as black box here, it
usually takes a column of values, or a row of values, treating these
cells internally as a "vector" in C++). I have verified that the
function works properly.

But now, what if I want to select cells which are scattered around
and
not arranged in one row or one column ...

It's like a multiple selection, and I don't have criteria for VSelect
-- the cells should be selected by hand, no uniform criteria for any
automatic selection...

Please help me!

Thanks a lot!