Smurphster16
New Member
- Joined
- Feb 28, 2019
- Messages
- 25
Hi ,
I have written a piece of VBA code which creates two dynamic arrays (array1 comprises of dates and array2 monetary values).
I want to use the two dynamic arrays to create a formula in my excel worksheet that adds together several sumif formulas. The number of sumif formulas will be the same as the size of both dynamic arrays (which is the same size as the value of payment dates which I have previously defined)
The sum ifs in the formula need to use the first value of array1 as the range criteria for the first sumif and the first value of array 2 as the sum range for the first sumif and so on.
preferably I would enter the combined sumif formula as a formula into my worksheet rather than calculating in visual basic, please find the first snippet of my code below.
Is there a way to enter the formula using ActiveCell.FormulaR1C1? and a loop inside of this? as payment dates could vary every time the code is executed?
Thanks in advance ,
Phil
I have written a piece of VBA code which creates two dynamic arrays (array1 comprises of dates and array2 monetary values).
I want to use the two dynamic arrays to create a formula in my excel worksheet that adds together several sumif formulas. The number of sumif formulas will be the same as the size of both dynamic arrays (which is the same size as the value of payment dates which I have previously defined)
The sum ifs in the formula need to use the first value of array1 as the range criteria for the first sumif and the first value of array 2 as the sum range for the first sumif and so on.
preferably I would enter the combined sumif formula as a formula into my worksheet rather than calculating in visual basic, please find the first snippet of my code below.
Code:
ReDim array1(payment_dates)
ReDim array2(payment_dates)
d = -8 + (-(payment_dates * 4))
e = -5 + (-(payment_dates * 4))
For i = 1 To payment_dates
d = d + 4
e = e + 4
array1(i) = Range("bn86").Offset(0, d)
array2(i) = Range("bn86").Offset(0, e)
Is there a way to enter the formula using ActiveCell.FormulaR1C1? and a loop inside of this? as payment dates could vary every time the code is executed?
Thanks in advance ,
Phil