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

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

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" <[email protected]> wrote in message
news:#[email protected]...
> 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

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" <[email protected]> wrote in message
> news:#[email protected]...
>
>>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
>
>
>

It gives me the runtime error: Method 'Range' of object '_WorkSheet' failed

- Mangesh


"Alan Beban" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:#[email protected]...
> >
> >>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
> >
> >
> >

Which line fails?

Alan Beban

Mangesh wrote:
> It gives me the runtime error: Method 'Range' of object '_WorkSheet' failed
>
> - Mangesh
>
>
> "Alan Beban" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
>>>news:#[email protected]...
>>>
>>>
>>>>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
>>>
>>>
>>>
>
>

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

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

is this what you are trying to do?

Sheet2.Cells(2, 2).Reize(Ubound(FinaOutpt,1),Ubound(FinalOutput,2)).Value = FinalOutput