(Solved) – VBA – how to use the LinEst function and return values in a column of variable length


I am trying to use the LinEst function to take values from a range of rows of data and input them into a new sheet under some headings. I only want to do this for a particular number of rows (up to row number defined as “c”. My VBA skills are very basic.

Sub Button9_Click()

Dim xrng As Range
Dim yrng As Range
Dim x2rng As Range
Dim y2rng As Range
Dim i As Integer
Dim Rng As Range
Dim l As Integer
Dim k As Integer
Dim c As Integer
Dim drop As Range
Dim drop2 As Range


Dim lv As String
    lv = Worksheets("Template").Range(Worksheets("Template").Range("B11"), Worksheets("Template").Range("B11").End(xlDown)).Find(WorksheetFunction.Small(Worksheets("Template").Range(Worksheets("Template").Range("B11"), Worksheets("Template").Range("B11").End(xlDown)), 1), , , 1).Address
l = ActiveCell.Row

k = Worksheets("Template").Range(Worksheets("Template").Range("B11"), Worksheets("Template").Range("B11").End(xlDown)).Rows.Count

c = l - 10

i = 1

Sheets.Add.Name = "Down Sweep Power Law"

Set xrng = Worksheets("Template").Range("C11:CP11")
Set yrng = Worksheets("Template").Range("C201:CP201")
Set drop = Worksheets("Down Sweep Power Law").Range("A2")
Set x2rng = xrng.Offset(1, 0)
Set y2rng = yrng.Offset(1, 0)
Set drop2 = drop.Offset(1, 0)

Worksheets("Down Sweep Power Law").Range("A1").Value = "(n-1) Value"
Worksheets("Down Sweep Power Law").Range("B1").Value = "log(k) Value"
Worksheets("Down Sweep Power Law").Range("C1").Value = "n Value"
Worksheets("Down Sweep Power Law").Range("D1").Value = "k Value"
Worksheets("Down Sweep Power Law").Range("E1").Value = "R Value"

If i < c Then

drop = Evaluate("LinEst(yrng, xrng, True, False)")
i = i   1

End If


If i < c Then

drop2 = Evaluate("LinEst((y2rng)), ((x2rng)), True, False)")

x2rng = x2rng.Offset(1, 0)
y2rng = y2rng.Offset(1, 0)
drop2 = drop2.Offset(1, 0)
i = i   1

End If

End Sub

the code runs but when I go on the created sheet, there is a #NAME error (2029) and no values are present.

Is there a way to fix this?

Any help would be appreciated.



Leave a Reply

Your email address will not be published. Required fields are marked *