Using variables in a range statement - how do I get this to work pls?

[Mightyginger]

I have a varialble called column which has the correct column number. The top row is always going to be 3 and the last row is always (no_months+3). I thought the following would work but it doesn't.

The function it's using is an interpolation function where the inputs are CurveInterp(x vals, y vals, x seek). The x vals and y vals are fixed, won't changed but I need the last bit to work.

This works at the moment but is missing the last arguement for the interpolation.

Sheet1.Range(Sheet1.Cells(3, column + 2), Sheet1.Cells((no_months + 3), (column + 2))).FormulaArray = _
"=CurveInterp(""loglinear""," & "Curve_Config!AJ4:AJ38,Curve_Config!AK4:AK38," & ")"

This is what I'd tried but it's giving me an error saying "FormulaArray property of the Range class"
Sheet1.Range(Sheet1.Cells(3, column + 2), Sheet1.Cells((no_months + 3), (column + 2))).FormulaArray = _
"=CurveInterp(""loglinear""," & "Curve_Config!AJ4:AJ38,Curve_Config!AK4:AK38,R[3]C[" & column & "]:R[" & (no_months + 3) & "]C[" & column & "])"

int this case the hoped output would have been an array on sheet one in range C3:C20 saying {=CurveInterp("loglinear",Curve_config!AJ4:AJ38,Curve_Config!AK4:AK38,A3:A20)}

Hope this is clear enough.


Thanks,


Neil.

 

[SkipVought]

Neil,

Much easier to define a range using

Set MyRange = Range(Cells(Row1, Col1), Cells(Row2, Col2))

then insert in a sheet formula like (for simplicity)

  SomeOtherRange.Formula = "=" & MyRange.Address

So in your case

Set rng1 = range(cells(TopRow+1 ,"AJ"), cells(LastRow, "AJ"))
Set rng2 = range(cells(TopRow+1 ,"AK"), cells(LastRow, "AK"))
Set rng2 = range(cells(TopRow+1 ,"A"), cells(LastRow, "A"))

Sheet1.Range(Sheet1.Cells(3, column + 2), Sheet1.Cells((no_months + 3), (column + 2))).FormulaArray = _
        "=CurveInterp(""loglinear"",Curve_Config!" & rng1.address & ",Curve_Config!" & rng2.address & "," & rng3.address & ")"

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}