CODEWITHSUNDEEP
vbaexcel
kaleassv
kaleassv

Reputation: 1

Set Range from another Range name

I am new with excel VBA. I am trying to make a function in my workbook which will do the excel linest function. I have a range (pqr) in which the first column is Y-variables and second column is X-Variable. I want to assign R1 and R2 as variables in the code. I tried to get code from other threads but this below is the code which I wrote...

Function LinestTest(pqr As Range)
 Dim vCoeff As Variant
 Dim r1 As Range
 Dim r2 As Range
 Set r1 = Range(pqr).Offset(, 1)
 Set r2 = Range(pqr).Offset(, 0)
 vCoeff = WorksheetFunction.LinEst(r1, Application.Power(r2, Array(1, 2, 3)))
 LinestTest = vCoeff

If I pass r1 and r2 separately, the code works.

Upvotes: 0

Views: 361

Answers (2)

kaleassv
kaleassv

Reputation: 1

This worked out..

    Function LinestTest(pqr As Range)
    Dim vCoeff As Variant
    Dim r1 As Range
    Dim r2 As Range
    'Set r1 = pqr.Offset(, 1)
    'Set r2 = pqr.Offset(, 2)
    Set r2 = pqr.Offset(0, 0).Resize(pqr.Rows.Count, pqr.Columns.Count - 1)
    Set r1 = pqr.Offset(0, 1).Resize(pqr.Rows.Count, pqr.Columns.Count - 1)

    vCoeff = WorksheetFunction.LinEst(r1, Application.Power(r2, Array(1, 2, 3)))
    LinestTest = vCoeff
End Function

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

Since pqr is defined as Range, you need to apply the Offset property directly on it.

Replace:

Set r1 = Range(pqr).Offset(, 1)
Set r2 = Range(pqr).Offset(, 0)

with:

Set r1 = pqr.Offset(, 1)
Set r2 = pqr.Offset(, 0)

B.T.W

Set r2 = pqr.Offset(, 0) is equivalent to Set r2 = pqr

Upvotes: 1

Related Questions