G
Guest
Need some help,
Here is what i'm trying to do
I'm calculating the YTD Operational variance for about 1,200 rows (Prior
year and Budget). Icould do this easily on the worksheet but it would take up
too many columns.
I would like to beable to have the results of the formula from a cell be
loaded in an array. Depending on what month the cell could hold 1 to 12
results.
If it was YTD dec the the array would hold the results for 12 calculations
(one for each month)
Here is my current approach
Sub YTD_Ops_Var_Calc()
Range("FQ17").Activate
For X = 1 To 12
ActiveCell.FormulaR1C1 =
"=(RC[-61]/RC[-64])*(INDEX(FX_Rates,MATCH(RC179,INDEX(FX_Rates,0,1),0),R2C173)/INDEX(FX_Rates,MATCH(RC179,INDEX(FX_Rates,0,1),0),R2C174))"
RR = ActiveCell.Value
Next X
End Sub
RR captures the correct results, but i need capture the results for 12
calculations
Here is what i'm trying to do
I'm calculating the YTD Operational variance for about 1,200 rows (Prior
year and Budget). Icould do this easily on the worksheet but it would take up
too many columns.
I would like to beable to have the results of the formula from a cell be
loaded in an array. Depending on what month the cell could hold 1 to 12
results.
If it was YTD dec the the array would hold the results for 12 calculations
(one for each month)
Here is my current approach
Sub YTD_Ops_Var_Calc()
Range("FQ17").Activate
For X = 1 To 12
ActiveCell.FormulaR1C1 =
"=(RC[-61]/RC[-64])*(INDEX(FX_Rates,MATCH(RC179,INDEX(FX_Rates,0,1),0),R2C173)/INDEX(FX_Rates,MATCH(RC179,INDEX(FX_Rates,0,1),0),R2C174))"
RR = ActiveCell.Value
Next X
End Sub
RR captures the correct results, but i need capture the results for 12
calculations