Hi Bob,

You can think of a range variable as just a pointer to a block of Excel
cells, with lots of associated properties and methods for manipulating the
block of cells it is pointing to. It is not a copy of the cells, just a
pointer to them.

You can copy the values of the block of cells to a VBA variant and
manipulate the values in the variant, then write them back to a different
range. This is a lot faster than manipulating the cells directly one by one.

You can also copy other properties such as formulae or number format (but
only one property at a time) from the block of cells to VBA variants.

As Rick has pointed out the simplest way to do what you want is to copy the
range of cells somewhere else, then Set a Range variable that points to the
copy and then manipulate that.

Usually what I do is a combination of both approaches:
- assign the Range.Value2 to a variant to create a copy of the values in an
array
- manipulate the values in the resulting variant array
- assign the variant to a different range to write the values back
- copy and pastespecial the formats from the original range to the copy
- set a range variable to the copy
- use the range variable to manipulate formats etc

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com


"Bob" wrote in message
...
Hi everyone:

I had posted a similar question a while ago, and I never got an answer
that made sense to me. I am sure the experts did not understand my
question. Basically, I am trying to create a variable of type Range, dump
some cells into this variable, manipulate the contents (format, value,
etc) of the variable without affecting the original cells where the data
came from, and then dump my result in another area of the sheet. This
way, I keep the original data intact, and show the manipulated data as
well. I can come up with some work arounds on this, but I want to use a
variable just like
y=x
y=2*y+1
z=y

As you see in this example, y is a dummy variable and whatever I do to it,
it will not affect x. That is exactly what I am trying to achieve, but
with a range. Now, as I mentioned before, I need to manipulate values,
formats, color, character formats, etc.

I am not sure if this is at all possible. If it is, and someone has a
code or explanation on how to do this, I appreciate your help.

Bob