Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!

There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you! I should be able to adapt this code.

"Debra Dalgleish" wrote:

There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

This worked great! Thank you.

However, as my sample has now expanded beyond a couple of hundred pivot
items. There are now over two thousand unique items and I am faced with the
error that effectively says there are too many items for the pivotitems
field. (I believe this is a function of the number of unique items by the
columns adding up to some number which cannot be exceeded).

Is there a work around? I would like to be able to generate the results for
all the items at one shot as it speeds the work and precludes human error.

Many thanks.



"Debra Dalgleish" wrote:

There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

The page field should allow more than 2000 items. What version of Excel
are you using?


nrehman wrote:
This worked great! Thank you.

However, as my sample has now expanded beyond a couple of hundred pivot
items. There are now over two thousand unique items and I am faced with the
error that effectively says there are too many items for the pivotitems
field. (I believe this is a function of the number of unique items by the
columns adding up to some number which cannot be exceeded).

Is there a work around? I would like to be able to generate the results for
all the items at one shot as it speeds the work and precludes human error.

Many thanks.



"Debra Dalgleish" wrote:


There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:

Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you for your reply.

You are absolutely correct. Creating the identical parameters to cause the
same "bug", I found that I had created another variable which was crashing
the code. Once I removed that, the program ran fine (which is really great)!

I will be coming down to another wall soon, which the the maximum number of
rows (I have almost 65000 rows by 6 columns of data). Would you have any
ideas for how to work around this issue? Is there some way in the vb to
code/join two worksheets or range two sets of columns for a pivot table? (If
you prefer, I can submit a new item for the forum).

Thanks again for your invaluable help.

Nadir


"Debra Dalgleish" wrote:

The page field should allow more than 2000 items. What version of Excel
are you using?


nrehman wrote:
This worked great! Thank you.

However, as my sample has now expanded beyond a couple of hundred pivot
items. There are now over two thousand unique items and I am faced with the
error that effectively says there are too many items for the pivotitems
field. (I believe this is a function of the number of unique items by the
columns adding up to some number which cannot be exceeded).

Is there a work around? I would like to be able to generate the results for
all the items at one shot as it speeds the work and precludes human error.

Many thanks.



"Debra Dalgleish" wrote:


There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:

Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You could use multiple consolidation ranges, but the results are too
good. There's an example, and a couple of workarounds, he

http://www.contextures.com/xlPivot08.html


nrehman wrote:
Thank you for your reply.

You are absolutely correct. Creating the identical parameters to cause the
same "bug", I found that I had created another variable which was crashing
the code. Once I removed that, the program ran fine (which is really great)!

I will be coming down to another wall soon, which the the maximum number of
rows (I have almost 65000 rows by 6 columns of data). Would you have any
ideas for how to work around this issue? Is there some way in the vb to
code/join two worksheets or range two sets of columns for a pivot table? (If
you prefer, I can submit a new item for the forum).

Thanks again for your invaluable help.

Nadir


"Debra Dalgleish" wrote:


The page field should allow more than 2000 items. What version of Excel
are you using?


nrehman wrote:

This worked great! Thank you.

However, as my sample has now expanded beyond a couple of hundred pivot
items. There are now over two thousand unique items and I am faced with the
error that effectively says there are too many items for the pivotitems
field. (I believe this is a function of the number of unique items by the
columns adding up to some number which cannot be exceeded).

Is there a work around? I would like to be able to generate the results for
all the items at one shot as it speeds the work and precludes human error.

Many thanks.



"Debra Dalgleish" wrote:



There's sample code here for printing each item in the page field:

http://www.contextures.com/xlPivot09.html

You could adapt that to your workbook.

nrehman wrote:


Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish has some sample code at:
http://contextures.com/xlPivot09.html

I bet you could modify it to do what you want.

nrehman wrote:

Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.

The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the underlying cashflow change constantly.

How could I cycle through the investments and copy the results from the IRR
to a separate table? (I can create the copy routine, but have not been
successful with the cycling through the pivot table page field).

Many thanks!

--

Dave Peterson