VBA Forecast using variable ranges??? 1

[knifey]

Hi,
Can anyone advise me on the correct use of the Forecast function in a VBA loop. I have columns for each month of the financial year so the ranges of x, known y's and known x's are variable depending on what column is the current month's column. I have tried:

xToForecast = Worksheets("TOTALS").Columns(monthColumnInTotalsSheetLetter & ":" & monthColumnInTotalsSheetLetter).Rows("23:23").Address
forecastKnownValuesYstartCell = Worksheets("TOTALS").Columns("H:H").Rows(wb1endRowTotals - 2).Address
forecastKnownValuesYendCell = Worksheets("TOTALS").Columns(monthColumnInTotalsSheetMinusOneLetter & ":" & monthColumnInTotalsSheetMinusOneLetter).Rows(wb1endRowTotals - 2).Address
forecastKnownValuesYrange = forecastKnownValuesYstartCell & ":" & forecastKnownValuesYendCell
forecastKnownValuesXstartCell = Worksheets("TOTALS").Columns("H:H").Rows("23:23").Address
forecastKnownValuesXendCell = Worksheets("TOTALS").Columns(monthColumnInTotalsSheetMinusOneLetter & ":" & monthColumnInTotalsSheetMinusOneLetter).Rows("23:23").Address
forecastKnownValuesXrange = forecastKnownValuesXstartCell & ":" & forecastKnownValuesXendCell
params = """ & xToForecast & "","" & forecastKnownValuesYrange & "","" & forecastKnownValuesXrange & """
forecastResult = Application.Forecast(params)

...But this gives me an Invalid number of arguments error.
I have also tried:

forecastResult = Application.Forecast(xToForecast,forecastKnownValuesYrange,forecastKnownValuesXrange)

...But this gives me an error 2015 (x-value is not a numeric value?)
Am I doing this completely wrong? Do I need to create an array of all known x's and y's into a variable instead of just stating the ranges? Any advise would be much appreciated.
Thanks,
K

 

[PHV]

What about this ?
forecastResult = Application.Forecast(CDbl(Range(xToForecast).Value), Range(forecastKnownValuesYrange), Range(forecastKnownValuesXrange))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

The Address property returns string, you need to pass cells contents to Forecast function, so first of all remove ".Address" from first argument assignment and convert to range in case of the second and third one.
You build a single string in the "params" variable (first code block), the second trial should work when you properly define arguments (no ".Address").

However your way of referencing ranges works (or should work), it can be done immediately:

With Worksheets("TOTALS")
    xToForecast = .Cells(monthColumnInTotalsSheetLetter,23)
    KnownY = Range(.Cells(KnownYR1,KnownYC1),.Cells(KnownYR2,KnownYC2))
    KnownX = Range(.Cells(KnownXR1,KnownXC1),.Cells(KnownXR2,KnownXC2))
End with

I shortened range bounds row/column number names (they were too long for me to track the code).

combo