Creating a variable of type Range

[Bob]

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

 

[Rick Rothstein]

For the solution I have in mind... will the location you want to dump the
manipulated cells to be contiguous (whether the original cells were
contiguous or not)? For example, if your original cells are A1:C3, F4:F9,
G2:G7, then where to you want them when you dump them elsewhere on the
sheet?

 

[Bob]

Hi Rick:

Thanks for your help. Yes, the data will be continuous, and may expand on
more than one column. This is the same problem that we discussed on 9/28,
and 9/30. At this point, I am not so much concerned about dumping the data,
as storing my range in a variable, play with the variable, and not have the
original data changed.

Bob

 

[Rick Rothstein]

I don't think you can do what you asked, at least not directly. The only way
I can think of for you to do what you want is to copy the source cells to
the destination location and then to either manipulate the location cells
directly or assign them to your range variable and manipulate the cells by
using the range variable you assigned them to. For example, something like
this...

Sub Test()
Dim R As Range
With Range("A1:F9")
.Copy Range("X5")
Set R = Range("X5").Resize(.Rows.Count, .Columns.Count)
End With
' Now, manipulating R will manipulate the only
' the "dump to" range while leaving the original
' range intact.
End Sub

 

[Bob]

Thanks Rick. That was what I thought and was afraid of. Let me look at it
during the next couple of days, and I will get back to you. Thanks for your
help, and have a nice Sunday.

Bob

 

[Charles Williams]

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