VBA function w/ arrays, loops & if statements not working in Excel2010 on Win 8<o
></o>
<o></o>
OK, so I amwriting this function to place values in cells for ONLY the dates when thetransaction will be. I.E. I am making a "weekly" contributionto my savings account, so this would occur on date X, date X+7, etc up to dateX+28 (I only have 30 days of record in my "cash flow" tab. Itried this using "sumif" and "sumifs", but wasunsuccessful, as there are some "or" conditions that have to bemet. So I decided to write my own function below. I have writtensome functions before but nothing this complex. This is the data I have which will be sent to the formula:<o></o>
<o> </o>
[TABLE="class: MsoNormalTable, width: 3"]
<tbody>[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
[/TD]
[TD="width: 100, bgcolor: transparent"]
[/TD]
[TD="width: 99, bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
Option Explicit<o></o>
Option Base 1<o></o>
Function ret_value(pay_sched, trans_dates, trans_amounts, current_date)<o></o>
Dim Result() As Variant 'Resulting Array Of Values<o></o>
Dim Final As Double 'Final Value To Be Displayed In Cell<o></o>
Dim i As Integer 'Transaction Dates Array Size<o></o>
Dim j As Integer 'Transaction Amounts Array Size<o></o>
Dim count As Integer 'Counter<o></o>
i = Application.CountA(trans_dates)<o></o>
j = Application.CountA(trans_amounts)<o></o>
count = 1<o></o>
Final = 0<o></o>
Erase Result()<o></o>
If i = j Then<o></o>
For count = 1 To i<o></o>
If pay_sched(count) ="Weekly" And (current_date = trans_dates(count) _<o></o>
Or current_date =trans_dates(count) + 7 Or current_date = trans_dates(count) + 14 _<o></o>
Or current_date =trans_dates(count) + 21 Or current_date = trans_dates(count) + 28) Then<o></o>
Result(count) =trans_amounts(count)<o></o>
Else<o></o>
Result(count) = 0<o></o>
End If<o></o>
Next count<o></o>
count = 1<o></o>
For count = 1 To i<o></o>
Final = Final + Result(count)<o></o>
Next count<o></o>
ret_value = Final<o></o>
Else<o></o>
ret_value = "Error"<o></o>
End If<o></o>
End Function<o></o>
<o></o>
The “pay schedule would be entered into the “pay_sched” array, the nextdue date would be entered into the “trans_dates” array, the payment amountswould be entered into the “trans_amounts” array. The “current_date” represents the date I amadding the values together for (i.e. if 2 transactions occur on the same day, Iwant to add them, and display the total number in that 1 cell). I want the formula to output a single number(NOT AN ARRAY), so that I can use this for each day in the 30 day cash flowthat I have put together already. The formula currently only displays a #VALUE! error in the excel sheet. I tried to see where the function breaks down, but it seems to not be running through the loops properly (it will go through the loops the correct amount of times if I do NOTHING inside that section of the loop, i.e. comment out the formula).
<o></o>
Thanks in advance for any help you can provide!<o></o>
></o><o
></o>OK, so I amwriting this function to place values in cells for ONLY the dates when thetransaction will be. I.E. I am making a "weekly" contributionto my savings account, so this would occur on date X, date X+7, etc up to dateX+28 (I only have 30 days of record in my "cash flow" tab. Itried this using "sumif" and "sumifs", but wasunsuccessful, as there are some "or" conditions that have to bemet. So I decided to write my own function below. I have writtensome functions before but nothing this complex. This is the data I have which will be sent to the formula:<o
></o><o
> </o>[TABLE="class: MsoNormalTable, width: 3"]
<tbody>[TR]
[TD="width: 89, bgcolor: transparent"]
Pay Schedule<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
Payment Amnt<o></o>
></o>[TD="width: 99, bgcolor: transparent"]
Next Due Date<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Monthly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ 202.87 <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
5/25/2013<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Quarterly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ - <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
N/A<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Monthly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ 169.46 <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
5/17/2013<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Monthly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ 25.00 <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
N/A<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Weekly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ - <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
N/A<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Weekly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ - <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
N/A<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Weekly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ 100.00 <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
5/10/2013<o></o>
></o>[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]
Monthly<o></o>
></o>[TD="width: 100, bgcolor: transparent"]
$ 200.00 <o></o>
></o>[TD="width: 99, bgcolor: transparent"]
5/14/2013<o></o>
></o>[/TR]
</tbody>[/TABLE]
<o
></o>Option Explicit<o
></o>Option Base 1<o
></o>Function ret_value(pay_sched, trans_dates, trans_amounts, current_date)<o
></o>Dim Result() As Variant 'Resulting Array Of Values<o
></o>Dim Final As Double 'Final Value To Be Displayed In Cell<o
></o>Dim i As Integer 'Transaction Dates Array Size<o
></o>Dim j As Integer 'Transaction Amounts Array Size<o
></o>Dim count As Integer 'Counter<o
></o>i = Application.CountA(trans_dates)<o
></o>j = Application.CountA(trans_amounts)<o
></o>count = 1<o
></o>Final = 0<o
></o>Erase Result()<o
></o>If i = j Then<o
></o>For count = 1 To i<o
></o>If pay_sched(count) ="Weekly" And (current_date = trans_dates(count) _<o
></o>Or current_date =trans_dates(count) + 7 Or current_date = trans_dates(count) + 14 _<o
></o>Or current_date =trans_dates(count) + 21 Or current_date = trans_dates(count) + 28) Then<o
></o>Result(count) =trans_amounts(count)<o
></o>Else<o
></o>Result(count) = 0<o
></o>End If<o
></o>Next count<o
></o>count = 1<o
></o>For count = 1 To i<o
></o>Final = Final + Result(count)<o
></o>Next count<o
></o>ret_value = Final<o
></o>Else<o
></o>ret_value = "Error"<o
></o>End If<o
></o>End Function<o
></o><o
></o>The “pay schedule would be entered into the “pay_sched” array, the nextdue date would be entered into the “trans_dates” array, the payment amountswould be entered into the “trans_amounts” array. The “current_date” represents the date I amadding the values together for (i.e. if 2 transactions occur on the same day, Iwant to add them, and display the total number in that 1 cell). I want the formula to output a single number(NOT AN ARRAY), so that I can use this for each day in the 30 day cash flowthat I have put together already. The formula currently only displays a #VALUE! error in the excel sheet. I tried to see where the function breaks down, but it seems to not be running through the loops properly (it will go through the loops the correct amount of times if I do NOTHING inside that section of the loop, i.e. comment out the formula).
<o
></o>Thanks in advance for any help you can provide!<o
></o>
