"Method 'Range" of object "_Worksheet'

[brulet]

Any idea why "Method 'Range" of object "_Worksheet' failed?

it's the last line application.forecast call that fails.. smallertotheleft and right are doubles, smaller and larger are ranges.. Thanks.

BT

<code>

Sub generateTableDTQ(DTQsheet As String)
Dim smaller, larger, DTQvalues As Variant
Dim smalVal, largVal As Variant
Dim xRange As Range
Dim c As String

DTQvalues = Array(0.0001, 0.001, 0.01, 0.05, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.95, 0.99, 0.999, 0.9999)
With Application.Worksheets(DTQsheet)
lstrw = .Range("C" & Rows.Count).End(xlUp).Row
Set xRange = .Range(.Range("C1"), "C" & lstrw)
For i = 0 To 16

' smaller = Range(smallest(DTQvalues(i), xRange))
MsgBox smallest(DTQvalues(i), xRange)
Set smaller = smallest(DTQvalues(i), xRange)
Set larger = largest(DTQvalues(i), xRange)

.Cells(i + 1, 6).FormulaLocal = DTQvalues(i)
If smaller.Value = larger.Value Then
.Cells(i + 1, 7).FormulaLocal = smaller
Else
smallertotheleft = smaller.Cells.Offset(0, -1).Value
largertotheleft = larger.Cells.Offset(0, -1).Value
MsgBox TypeName(smaller.Value)
' c = "=forecast(" & DTQvalues(i) & "," & smallertotheleft & ":" & largertotheleft & ":" & smaller.Value & ":" & larger.Value & ")"
.Cells(i + 1, 7).FormulaLocal = Application.Forecast(DTQvalues(i), Range(smallertotheleft, largertotheleft), Range(smaller.Value, larger.Value))


End If
Next i
End With
End Sub

</code>

 

[RoryA]

That use of Range makes no sense to me. If the values in question are doubles, what is:
Range(smallertotheleft, largertotheleft)
supposed to be? For example, what would:
Range(15.6, 32.4)
mean?

 

[Jonmo1]

welcome to the board..

What exactly are you trying to refer to with these 2 ranges?
Range(smallertotheleft, largertotheleft)
Range(smaller.Value, larger.Value)


And what are the values of each variable at the time the code runs?


If smallertotheleft and largertotheleft are Doubles, then that is the problem
Range requires a column LETTER, like A1

Perhaps you want to use Cells instead

Cells(Row#, Column#)

 

[brulet]

Ahh! I'm an idiot.. It works when I use array() instead.. which is what forecast wants!

' c = "=forecast(" & DTQvalues(i) & "," & smallertotheleft & ":" & largertotheleft & ":" & smaller.Value & ":" & larger.Value & ")"

This line was causing problems.

Thanks again!