danmoparman
New Member
- Joined
- Nov 10, 2003
- Messages
- 4
Hi,
I am trying to create a macro that will calc an IRR for a given set of cash flows. To get the data I will be using a query that pulls all the dates and cash flows. To be specific the query will pull the fund name (column A), the Quarter Ending date (Column B), and the net cash flow (Column C). However, I will not be using the Quarter Ending date but rather the midpoint date of the quarter (the query cannot be altered to accomodate this) because it is incorrect for the calculation, so I set up an "index" sheet that the macro can use to lookup the quarter ending date and enter in the corresponding midpoint date.
The problem arises when I try to tell the macro to only perform the lookup for as many quarter ending dates as there are rows in the current worksheet because the query on each fund doesn't always return the same amount of rows due to some funds having more cash flows than others. Basically, I don't want to set a static range for the lookup but would rather have the lookup perform for as many dates as it has to.
If this makes sense to anybody your help would be greatly appreciated. Let me know if I need to elaborate.
thanks,
DC
I am trying to create a macro that will calc an IRR for a given set of cash flows. To get the data I will be using a query that pulls all the dates and cash flows. To be specific the query will pull the fund name (column A), the Quarter Ending date (Column B), and the net cash flow (Column C). However, I will not be using the Quarter Ending date but rather the midpoint date of the quarter (the query cannot be altered to accomodate this) because it is incorrect for the calculation, so I set up an "index" sheet that the macro can use to lookup the quarter ending date and enter in the corresponding midpoint date.
The problem arises when I try to tell the macro to only perform the lookup for as many quarter ending dates as there are rows in the current worksheet because the query on each fund doesn't always return the same amount of rows due to some funds having more cash flows than others. Basically, I don't want to set a static range for the lookup but would rather have the lookup perform for as many dates as it has to.
If this makes sense to anybody your help would be greatly appreciated. Let me know if I need to elaborate.
thanks,
DC