Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
Hello MS Excel Newsgroup, I have constructed (well almost) an application using Microsoft Excel 2007 SP2, VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. The other Worksheet contains user controls which the user can select a few predefined values that, kick various VBA routines off, and display the results on a Graph, on the user's Worksheet (and a few other results). The user would like to see the underlying matrix on their Worksheet. Given the parameters the user chooses, I can set a named Range on the user's Worksheet to the exact dimensions of it's data matrix, on the other Worksheet. Learning that; UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") - nor - UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value - nor - UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject work, is there any 'innate' functionality in Excel that permits this? Is looping or Array assignment to the range the better way of approaching it? -- Thanks and regards, Offace |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
Hi,
I can't get my head around your names but the syntax is like this Sheets("Sheet1").Range("myrange").Copy Destination:=Sheets("Sheet2").Range("A1") This copies the named range called "myrange" from sheet 1 to a1 of sheet 2 Mike "Offace" wrote: Hello MS Excel Newsgroup, I have constructed (well almost) an application using Microsoft Excel 2007 SP2, VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. The other Worksheet contains user controls which the user can select a few predefined values that, kick various VBA routines off, and display the results on a Graph, on the user's Worksheet (and a few other results). The user would like to see the underlying matrix on their Worksheet. Given the parameters the user chooses, I can set a named Range on the user's Worksheet to the exact dimensions of it's data matrix, on the other Worksheet. Learning that; UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") - nor - UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value - nor - UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject work, is there any 'innate' functionality in Excel that permits this? Is looping or Array assignment to the range the better way of approaching it? -- Thanks and regards, Offace |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
if your eranges are exactly the same size, then use the INDIRECT() function so on your main sheet type a range name in say A1 elasewhere select an area of the correct size and erray enter {=INDIRECT(A1) } "Mike H" wrote: Hi, I can't get my head around your names but the syntax is like this Sheets("Sheet1").Range("myrange").Copy Destination:=Sheets("Sheet2").Range("A1") This copies the named range called "myrange" from sheet 1 to a1 of sheet 2 Mike "Offace" wrote: Hello MS Excel Newsgroup, I have constructed (well almost) an application using Microsoft Excel 2007 SP2, VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. The other Worksheet contains user controls which the user can select a few predefined values that, kick various VBA routines off, and display the results on a Graph, on the user's Worksheet (and a few other results). The user would like to see the underlying matrix on their Worksheet. Given the parameters the user chooses, I can set a named Range on the user's Worksheet to the exact dimensions of it's data matrix, on the other Worksheet. Learning that; UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") - nor - UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value - nor - UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject work, is there any 'innate' functionality in Excel that permits this? Is looping or Array assignment to the range the better way of approaching it? -- Thanks and regards, Offace |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
Thank you very much Mike, works well, and not only that, your response also made
me see the stupidity of my ways in the form of an obvious syntax error. Now my post works too. See what happens when you look at something (that's incorrect) for to long???? Thanks again Mike. "Mike H" wrote in message ... | Hi, | | I can't get my head around your names but the syntax is like this | | Sheets("Sheet1").Range("myrange").Copy | Destination:=Sheets("Sheet2").Range("A1") | | This copies the named range called "myrange" from sheet 1 to a1 of sheet 2 | | Mike | | "Offace" wrote: | | | Hello MS Excel Newsgroup, | | I have constructed (well almost) an application using Microsoft Excel 2007 SP2, | VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store | 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. | The other Worksheet contains user controls which the user can select a few | predefined values that, kick various VBA routines off, and display the results on | a Graph, on the user's Worksheet (and a few other results). | | The user would like to see the underlying matrix on their Worksheet. Given the | parameters the user chooses, I can set a named Range on the user's Worksheet to | the exact dimensions of it's data matrix, on the other Worksheet. | | Learning that; | | UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") | | - nor - | | UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value | | - nor - | | UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = | MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject | | work, is there any 'innate' functionality in Excel that permits this? | | Is looping or Array assignment to the range the better way of approaching it? | | | -- | | Thanks and regards, | | Offace | | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
Glad I could help and thanks for the feedback
"Offace" wrote: Thank you very much Mike, works well, and not only that, your response also made me see the stupidity of my ways in the form of an obvious syntax error. Now my post works too. See what happens when you look at something (that's incorrect) for to long???? Thanks again Mike. "Mike H" wrote in message ... | Hi, | | I can't get my head around your names but the syntax is like this | | Sheets("Sheet1").Range("myrange").Copy | Destination:=Sheets("Sheet2").Range("A1") | | This copies the named range called "myrange" from sheet 1 to a1 of sheet 2 | | Mike | | "Offace" wrote: | | | Hello MS Excel Newsgroup, | | I have constructed (well almost) an application using Microsoft Excel 2007 SP2, | VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store | 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. | The other Worksheet contains user controls which the user can select a few | predefined values that, kick various VBA routines off, and display the results on | a Graph, on the user's Worksheet (and a few other results). | | The user would like to see the underlying matrix on their Worksheet. Given the | parameters the user chooses, I can set a named Range on the user's Worksheet to | the exact dimensions of it's data matrix, on the other Worksheet. | | Learning that; | | UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") | | - nor - | | UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value | | - nor - | | UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = | MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject | | work, is there any 'innate' functionality in Excel that permits this? | | Is looping or Array assignment to the range the better way of approaching it? | | | -- | | Thanks and regards, | | Offace | | | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign Ranges On Different Worksheets.
Wow...! That was not at all apparent from the Microsoft Excel Inbuilt Help, type "excel indirect function" into Google and it really opens that function up. It is actually really good for "on the fly" data range work. Thanks Patrick. Never occurred to me what that function was all about, never looked at it, thought it was vector geometry related by the name of it. Thanks again. "Patrick Molloy" wrote in message ... | | if your eranges are exactly the same size, then use the INDIRECT() function | | so on your main sheet type a range name in say A1 | elasewhere select an area of the correct size and erray enter {=INDIRECT(A1) } | | | | "Mike H" wrote: | | Hi, | | I can't get my head around your names but the syntax is like this | | Sheets("Sheet1").Range("myrange").Copy | Destination:=Sheets("Sheet2").Range("A1") | | This copies the named range called "myrange" from sheet 1 to a1 of sheet 2 | | Mike | | "Offace" wrote: | | | Hello MS Excel Newsgroup, | | I have constructed (well almost) an application using Microsoft Excel 2007 SP2, | VBA, on Windows XP Pro SP3. There are two Worksheets which one is used to store | 12 (small) matrices of numbers, each matrix encapsulated in it's own named Range. | The other Worksheet contains user controls which the user can select a few | predefined values that, kick various VBA routines off, and display the results on | a Graph, on the user's Worksheet (and a few other results). | | The user would like to see the underlying matrix on their Worksheet. Given the | parameters the user chooses, I can set a named Range on the user's Worksheet to | the exact dimensions of it's data matrix, on the other Worksheet. | | Learning that; | | UserSheet.Range("DataDump") = MatrixSheet.Range("MatrixData") | | - nor - | | UserSheet.Range("DataDump").Value = MatrixSheet.Range("MatrixData") .Value | | - nor - | | UserSheet.Range("DataDump").AnythingElsePertaining ToValueOrObject = | MatrixSheet.Range("MatrixData") .AnythingElsePertainingToValueOrObject | | work, is there any 'innate' functionality in Excel that permits this? | | Is looping or Array assignment to the range the better way of approaching it? | | | -- | | Thanks and regards, | | Offace | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Programming | |||
Assign named ranges | Excel Programming | |||
how to assign ranges on different sheets to an array | Excel Programming | |||
how to assign ranges on different sheets to an array | Excel Programming |