Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
I am trying to copy some data from one worksheet to another so I can work
with it, but need to indirect reference the range. Cell D73 has starting Date cell reference and C72 has ending date cell reference so would like to use their contents for my Range reference. You can see I have tried using & to build the reference. I know that Range(Range("D73").Value works for a single cell but need to reference more than one cell? After I get the Range problem worked out will work on Offseting it so it covers the area I need. Just Stuck for now. Worksheets("06 PLATER 1 JAN-JUNE").Range(& Range("D73").Value &":" & Range("C72").Value).Copy _ Destination:=Worksheets("Reports").Range("$A$1") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Jerry,
Change your equation as follows: Worksheets("06 PLATER 1 JAN-JUNE").Range(Range("D73").Address & ":" & Range("C72").Address).Copy _ Destination:=Worksheets("Reports").Range("$A$1") *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
What you have should work if D73 contains something like F21 and C72
contains something like F35 if not, what do they contain? -- Regards, Tom Ogilvy "Jerry Wayland" <Jerry wrote in message ... I am trying to copy some data from one worksheet to another so I can work with it, but need to indirect reference the range. Cell D73 has starting Date cell reference and C72 has ending date cell reference so would like to use their contents for my Range reference. You can see I have tried using & to build the reference. I know that Range(Range("D73").Value works for a single cell but need to reference more than one cell? After I get the Range problem worked out will work on Offseting it so it covers the area I need. Just Stuck for now. Worksheets("06 PLATER 1 JAN-JUNE").Range(& Range("D73").Value &":" & Range("C72").Value).Copy _ Destination:=Worksheets("Reports").Range("$A$1") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Edward,
The Cells D73 and C72 have the Address I want in them so don't want their address but want to use the Address that is stored in them. Also using Excell 97 and changed my macro to yours but still syntax error? "Edward Ulle" wrote: Jerry, Change your equation as follows: Worksheets("06 PLATER 1 JAN-JUNE").Range(Range("D73").Address & ":" & Range("C72").Address).Copy _ Destination:=Worksheets("Reports").Range("$A$1") *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Ed,
Doesn't that just give the equivalent of Worksheets("06 PLATER 1 JAN-JUNE").Range("D73:C72").Copy _ Destination:=Worksheets("Reports").Range("$A$1") If that is what he wants, then no need to use anything else, but I don't think that is it. -- Regards, Tom Ogilvy "Edward Ulle" wrote in message ... Jerry, Change your equation as follows: Worksheets("06 PLATER 1 JAN-JUNE").Range(Range("D73").Address & ":" & Range("C72").Address).Copy _ Destination:=Worksheets("Reports").Range("$A$1") *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Edward Ulle,
Thank You did not see the second & put it in the macro and it worked. Again Thank You Jerry "Edward Ulle" wrote: Jerry, Change your equation as follows: Worksheets("06 PLATER 1 JAN-JUNE").Range(Range("D73").Address & ":" & Range("C72").Address).Copy _ Destination:=Worksheets("Reports").Range("$A$1") *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Jerry,
I misread your post. Tom is right. My suggestion will only copy the contents of the cells not the cells that are being referenced by them. Ed *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect Referencing in a Macro
Jerry,
Sorry for the misfire. Using the Range Precedents property you can access the cells that a simple formula such as "=A21" points to. Using your example. Dim r1 As Range Dim r2 As Range Set r1 = Worksheets("06 PLATER 1 JAN-JUNE").Range("D73").Precedents Set r2 = Worksheets("06 PLATER 1 JAN-JUNE").Range("C72").Precedents Worksheets("06 PLATER 1 JAN-JUNE").Range(r1.Address,r2.Address).Copy _ Destination:=Worksheets("Reports").Range("$A$1") *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing formula using Indirect | Excel Worksheet Functions | |||
Indirect referencing of external spreadsheets | Excel Discussion (Misc queries) | |||
Indirect referencing closed spreadsheets | Excel Discussion (Misc queries) | |||
Indirect Range Referencing | Charts and Charting in Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |