How to create a range in code
 

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.

How to create a range in code
 

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Bob" wrote:

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.

How to create a range in code
 

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

"Barb Reinhardt" wrote:

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Bob" wrote:

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.

How to create a range in code
 

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

--


Gary

"Bob" wrote in message
...
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.

How to create a range in code
 

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

--
HTH...

Jim Thomlinson


"Bob" wrote:

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

"Barb Reinhardt" wrote:

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Bob" wrote:

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.

How to create a range in code
 

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

"Bob" wrote:

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.