Easy Range question

[wheelie pete]

I'm trying to calculate the slope of 5 points in a rather large data set.

I used this bit of code:


Set xl = Range(1, 2, 3, 4, 5)
Set yl = Range(Cells(row - 5, col - 5), Cells(row, col - 5))
slopel = Application.WorksheetFunction.slope(xl, yl)

I'm trying to the set the range as just numbers, and not as cells. It doesnt like the first line.

Any ideas how to change it?

Thanks! Pete

 

[JoeMo]

Maybe:

Sub pete()
Dim xl As Variant, yl As Range
xl = Array(1, 2, 3, 4, 5)
Set yl = Range("R5:R9")    ' Change range to suit. For example try R5:R9 = {10,20,30,40,50}
MsgBox "Slope = " & WorksheetFunction.Slope(yl, xl)
End Sub

 

[wheelie pete]

Thanks!!!

It presents a type mismatch error on this line xl = Array(1, 2, 3, 4, 5)

Any idea?

 

[wheelie pete]

Nevermind! I figured that part out! Thanks

 

[wheelie pete]

Sorry - it still doesn't work. The Slope function does not appear to like the array function.

Right now, the code looks like this:

Dim yl As Range
Dim xl As Variant

xl = Array(1, 2, 3, 4, 5)
Set yl = Range(Cells(row - 5, col - 5), Cells(row, col - 5))
slopel = Application.WorksheetFunction.slope(yl, xl)

It gives me this error - runtime error 1004 - Unable to get the slope property of the worksheet function class.

Anyideas??

Thanks! Wheelie Pete

 

[JoeMo]

Can't help unless you show us what's in Range(Cells(row - 5, col - 5), Cells(row, col - 5)). Don't know how the row and col variables are defined.

 

[shg]

xl contains 5 elements; yl contains 6.

 

[wheelie pete]

Home run shg! That did it! Thanks so much!! - wheelie pete

 

[shg]

You're welcome.