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.

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.
>

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.
> >

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" <(E-Mail Removed)> wrote in message
news:33864FC4-F770-4F1E-A133-(E-Mail Removed)...
> 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.
>

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.
> > >

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.
>