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..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!