Loading data into an array
 

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

--
Helping Is always a good thing

Loading data into an array
 

Hi,

I hope this is a goal, try this:
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 = rr & vbNewLine & CStr(ActiveCell.Value)
Next X
MsgBox rr
End Sub
--

Regards,

Halim


"QuietMan" wrote:

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

--
Helping Is always a good thing