How to create a range in code

[Bob]

Hi Everyone:

I was wondering how can I create a range in code, and manipulate it without
affecting my sheets. For example, I need to create a range called R1, dump
some range into it, amnupulate R1 without affecting the original range. Here
is a code below that I am trying to achieve.


Dim R1 As Range
R1 = Range("B7:C12")
R1.Cells(3, 2) = 988


So, basically, dump the range B7:C12 into R1, cange one of its cell values,
but not to change the actual range B7:C12. I hope this makes sense. Thanks
for all your help.

Bob

PS. in the above code if I say Set R1 = Range("B7:C12") , the
following line changes the value of cell C9, which is not what I want.

 

[Barb Reinhardt]

What cell do you want to act on with this line?

R1.Cells(3, 2) = 988

If you use R1 = Range("B7:C12")

You'll be acting on the cell 2 rows below and 1 row to the right of B7.

 

[Bob]

Hi Barb:

You did not answer my question on how to create this range R1. Once the
range is created, I know how to manipulate it.

Bob

 

[Gary Keramidas]

this may help, or it may not,

the upper left cell in a range is "A1"

so if you wanted C9, the relative range would be range("B3") or cells(3,2)

Sub test()
Dim rng As Range
Set rng = Range("B7:C12")
MsgBox rng.Range("b3").Value
MsgBox rng.Cells(3, 2).Value
End Sub

 

[Jim Thomlinson]

A range such as R1 is an object so it requires the Set statement. No 2 ways
around it. Your issue is with the next line of code. It is looking in the
range that was created and move 2 rows below and 1 row to the right (so cell
C9) and changing that value just as you have directed...

Cell B7 -> 1, 1
Cell B8 -> 2, 1
Cell B9 -> 3, 1
Cell C9 -> 3, 2

 

[Bob]

guys, I am trying to create a variable that holds a range. Just like when
you say
dim x as long
x=range("A1").value
x=5

the value of cell A1 does not change because x changed. I am trying to use
a variable that holds a range, and I want to amnipulate the range in the
variable, without affecting my original source range.

Bob