• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Excel Range from variable

O

OldDog

#1
Hi,

I am writing a script that will copy certain cells from on sheet to
another. The problem I am having is that the cells I need can appear
in diferent places in the original sheet.

I can copy a range like this:

ws1.Range("C3:C" & row).Copy(ws2.Range("A2"))

However if I try to change the row and column to a variable it does
not work.

I have tried:

ws1.Range(.Cells(rRow & "," & rCol).Cells(row & "," &
rCol)).Copy(ws2.Range("B2"))

And this;

ws1.Range(rRow & "," & rCol & ":" & row & "," &
rCol).Copy(ws2.Range("D2"))

Any ideas?
 

My Computer

J

John Guillory

#2
To: OldDog
Re: Excel Range from variable
By: OldDog to microsoft.public.scripting.vbscript on Thu Apr 15 2010 11:36 am

> ws1.Range("C3:C" & row).Copy(ws2.Range("A2"))
> However if I try to change the row and column to a variable it does
> not work.
Try "Named Ranges", where you name the range of sells a meaningufl name....

--- Synchronet 3.15a-Win32 NewsLink 1.92
mlcgroup bbs - telnet://roachguts.com
 

My Computer

B

Brian Kelly

#3
OldDog wrote:

> However if I try to change the row and column to a variable it does
> not work.
>
> I have tried:
>
> ws1.Range(.Cells(rRow & "," & rCol).Cells(row & "," &
> rCol)).Copy(ws2.Range("B2"))
>
> And this;
>
> ws1.Range(rRow & "," & rCol & ":" & row & "," &
> rCol).Copy(ws2.Range("D2"))
>
> Any ideas?
Use the cells property of the range object and use notation like cells(x,y)
where x and y are integers e.g ws1.Range.cells(1,1) see
http://www.anthony-vba.kefra.com/vba/vbabasic2.htm#Range_Object_and_Cells_Property
for examples

--
Brian Kelly
Home Page http://kellybk.com
Species Race http://speciesrace.com
 

My Computer

Users Who Are Viewing This Thread (Users: 1, Guests: 0)