Results 1 to 5 of 5

Thread: Variable Range in VBA Regression

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Variable Range in VBA Regression

    Hello,

    I am trying to automate a linear regression but want it to work for variable sized data sets. This is what I have for a given set. The Y data always starts at X5 and the X data always starts at Y5, but the length of the column changes. Any ideas as to how I would allow for the regression to fit the data that is input (i.e. have the "$X$106 and $AG$106 change depending on data)?? Thanks for the help!

    Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$X$5:$X$106"), _
    ActiveSheet.Range("$Y$5:$AG$106"), False, False, , "", False, False, _
    False, False, , False

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Variable Range in VBA Regression

    Try this

    Code:
    Sub test()
      Dim x As Long, y As Long
      x = Range("X" & Rows.Count).End(xlUp).Row
      y = Range("Y" & Rows.Count).End(xlUp).Row
      Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$X$5:$X$" & x), _
        ActiveSheet.Range("$Y$5:$AG$" & y), False, False, , "", False, False, _
        False, False, , False
    End Sub
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variable Range in VBA Regression

    Thank you for your reply. I tried your suggestion but it returns an error "Regression - LINEST() function returns error. Please check input ranges again."

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variable Range in VBA Regression

    I figured it out!! It was taking into account empty cells below that had a hidden error message. Your code worked!! Thank you very much. It is appreciated!

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Variable Range in VBA Regression

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •