+ Reply to Thread
Results 1 to 3 of 3

Variable inputs to Linest

Hybrid View

  1. #1
    Registered User
    Join Date
    05-08-2020
    Location
    Pennsylvania, USA
    MS-Off Ver
    2016
    Posts
    35

    Variable inputs to Linest

    Hi,

    I'm trying to assign variable ranges as input to Linest, using 2 independent variables. The following code fails. I'm sure this is something simple, but Im stumped.

    Sub blind()
    Dim J As Integer
    Dim x As Range, y As Range
    
    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
    Set ws3 = Worksheets("sheet4")
    
    ws1.Activate
    J = 4  'I want this to be a variable, but setting it to 4 as a test
    Set y = Range(Cells(2, 2), Cells(J, 2))
    Set x = Range(Cells(2, 3), Cells(J, 4))
    
    ws3.Activate
    Range("g2: i6") = Application.LinEst(y, x, True, True)
    
    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,493

    Re: Variable inputs to Linest

    You would not have to activate the ws, but qualify the range.
    For example
    Notice "with" and the period
    with ws1
    J = 4  'I want this to be a variable, but setting it to 4 as a test
    Set y = .Range(.Cells(2, 2), .Cells(J, 2))
    Set x = .Range(.Cells(2, 3), .Cells(J, 4))
    end with

  3. #3
    Registered User
    Join Date
    05-08-2020
    Location
    Pennsylvania, USA
    MS-Off Ver
    2016
    Posts
    35

    Re: Variable inputs to Linest

    Thank you. I figured it was something straightforward

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Blank column as x variable & LINEST()
    By ell_ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2019, 10:25 PM
  2. Replies: 2
    Last Post: 10-04-2018, 04:24 PM
  3. [linest] Whu LINEST can't the precise parameter of each variable?
    By valuex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2015, 12:12 PM
  4. [SOLVED] Variable number of lines in a Linest
    By marcinhafernandez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2014, 04:16 PM
  5. Using linest for a variable range
    By shaolinsnake in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2012, 11:43 PM
  6. Formula with 3 variable inputs
    By DJ KooPee in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-26-2011, 10:57 AM
  7. Passing Variable to LINEST
    By RW in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1