Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
my code generates output in an array of m by n dimensions (m rows and n
columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
One way:
Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
No it doesn't. A slight modification:
Set rng = Sheet2.Range("b2") Range(rng.Address, rng.Offset(m - 1, n - 1).Address) = FinalOutput - Mangesh "Alan Beban" wrote in message ... One way: Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Hi Pradip,
Maybe you can use my code: Sub TestMyArray() 'Declare array 'But, remember to start it from 1 'So, do not start it from 0 Dim FinalOutput(1 To 2, 1 To 4) As Integer FinalOutput(1, 1) = 100 FinalOutput(1, 2) = 200 FinalOutput(1, 3) = 300 FinalOutput(1, 4) = 400 FinalOutput(2, 1) = 170 FinalOutput(2, 2) = 270 FinalOutput(2, 3) = 370 FinalOutput(2, 4) = 470 'Call array and paste Range("b2").Resize(2, 4) = FinalOutput End Sub Hope it works. Regards, Noor Faiz "Pradip Jain" wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Thanks Noor.
Your code works. "Noor Faiz" wrote: Hi Pradip, Maybe you can use my code: Sub TestMyArray() 'Declare array 'But, remember to start it from 1 'So, do not start it from 0 Dim FinalOutput(1 To 2, 1 To 4) As Integer FinalOutput(1, 1) = 100 FinalOutput(1, 2) = 200 FinalOutput(1, 3) = 300 FinalOutput(1, 4) = 400 FinalOutput(2, 1) = 170 FinalOutput(2, 2) = 270 FinalOutput(2, 3) = 370 FinalOutput(2, 4) = 470 'Call array and paste Range("b2").Resize(2, 4) = FinalOutput End Sub Hope it works. Regards, Noor Faiz "Pradip Jain" wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Mangesh wrote:
No it doesn't. Yeah it does; now I've tested it. What happens when you do? Alan Beban A slight modification: Set rng = Sheet2.Range("b2") Range(rng.Address, rng.Offset(m - 1, n - 1).Address) = FinalOutput - Mangesh "Alan Beban" wrote in message ... One way: Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
It gives me the runtime error: Method 'Range' of object '_WorkSheet' failed
- Mangesh "Alan Beban" wrote in message ... Mangesh wrote: No it doesn't. Yeah it does; now I've tested it. What happens when you do? Alan Beban A slight modification: Set rng = Sheet2.Range("b2") Range(rng.Address, rng.Offset(m - 1, n - 1).Address) = FinalOutput - Mangesh "Alan Beban" wrote in message ... One way: Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Which line fails?
Alan Beban Mangesh wrote: It gives me the runtime error: Method 'Range' of object '_WorkSheet' failed - Mangesh "Alan Beban" wrote in message ... Mangesh wrote: No it doesn't. Yeah it does; now I've tested it. What happens when you do? Alan Beban A slight modification: Set rng = Sheet2.Range("b2") Range(rng.Address, rng.Offset(m - 1, n - 1).Address) = FinalOutput - Mangesh "Alan Beban" wrote in message ... One way: Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Sorry, my mistake.
I didn't see the Sheet2, and tried on sheet1. Your code works perfectly fine. Mangesh "Alan Beban" wrote in message ... Which line fails? Alan Beban Mangesh wrote: It gives me the runtime error: Method 'Range' of object '_WorkSheet' failed - Mangesh "Alan Beban" wrote in message ... Mangesh wrote: No it doesn't. Yeah it does; now I've tested it. What happens when you do? Alan Beban A slight modification: Set rng = Sheet2.Range("b2") Range(rng.Address, rng.Offset(m - 1, n - 1).Address) = FinalOutput - Mangesh "Alan Beban" wrote in message ... One way: Set rng = Sheet2.Range("b2") Range(rng(1,1),rng(10,15)) = FinalOutput I didn't test the above, but if your code works, it should work. Alan Beban Pradip Jain wrote: my code generates output in an array of m by n dimensions (m rows and n columns). Number of rows and columns depends on the input. now i want to paste the result to sheet2 starting from cell B2. one of the way is as under: For ctr15 = 1 To n For ctr16 = 1 To m Sheet2.Cells(1 + ctr16, 1 + ctr15).Value = FinalOutput(ctr16, ctr15) Next ctr16 Next ctr15 This works fine, but the problem is it is too slow. excel paste the result cell by cell and is very slow. Much faster code is when entire array is pasted at a time. assuming 10 rows by 15 column output, code is: Sheet2.Range("b2:p11") = FinalOutput This also works. But problem is how to generalize the above code based on different values of m and n, i.e. different rows and columns. I tried the specifying just the corner left cell of the range, but that does not works Sheet2.Range("b2") = FinalOutput this just returns value on cell b2 please help regards pradip |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting array output to sheet
Mangesh wrote:
Sorry, my mistake. I didn't see the Sheet2, and tried on sheet1. Your code works perfectly fine. Mangesh Thanks for the feedback. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a List Output from a Horizontal Array Input | Excel Worksheet Functions | |||
customise array formula output | Excel Discussion (Misc queries) | |||
UDF, Array function, vertical output | Excel Programming | |||
Add value to array based on if...then output in VBA | Excel Programming | |||
Help with 1 x 2 array output | Excel Programming |