Set Range from another Range name

Hi,

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 thread 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 = pqr.Offset(, 1)
    Set r1 = pqr.Offset(, 2)
    vCoeff = WorksheetFunction.LinEst(r1, Application.Power(r2, Array(1, 2, 3)))
    LinestTest = vCoeff
    End Function

This is giving me #Value error when I call the function in excel. I tried passing r1 and r2 as independent variable and the code is working fine. This means the r1 and r2 assignments are not working. Can someone please help.

Re: Set Range from another Range name

Thanks MickG for reply. Indeed r1 was a typo. I managed to get the code working. Below is the working code..

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

Re: Set Range from another Range name

Thanks.. much better compared to other solution..