MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Linest Function

  Asked By: Nicole    Date: Nov 28    Category: MS Office    Views: 1459

I am trying to insert the LINEST function into my macro. The problem
i am having is that when recordinging it uses 'range'
Is there a way i can use the 'Cells' command as the amount of data to
be used in the function will differ each time i want to use it. Or is
there a way to insert a variable ie x if x = 1 into a range command



3 Answers Found

Answer #1    Answered By: Velma Adams     Answered On: Nov 28

Sure, eg. replace
range("F3:F6") with
or somewhere in the macro  do something along these lines:
myrange = range(cells(3,6),cells(6,6))
and use
in place of
in the linest formula.

Answer #2    Answered By: Wilbur Hall     Answered On: Nov 28

You could also put variables there in place of the (row,column) spec.

something like

dim rlRange as range
dim ilRow1 as integer
dim ilCol1 as integer
dim ilRow2 as integer
dim ilCol2 as integer

rlRange = Range(Cells(ilRow1,ilCol1),Cells(ilRow2,ilCol2))

Answer #3    Answered By: Alfonsine Miller     Answered On: Nov 28

Let me add 2 more hints...

1) If you have a table with variable  length, and/or width, you can use:
FirstRow = ActiveSheet.UsedRange.Row
FirstCol = ActiveSheet.UsedRange.Column
LastRow = FirstRow + ActiveSheet.UsedRange.Rows.Count - 1
LastCol = FirstCol + ActiveSheet.UsedRange.Columns.Count - 1

LastRow and LastCol sounds complicated, but makes perfect sense in case your
table does NOT start in row "1" and/or column "A". Then it is adjusting the row
and/or column number. You can use this in Pascal's range  very easily, for
myrange = range(cells(FirstRow,6),cells(LastRow,6))

2) If you know the first row and column, you can use:
Range(Cells(2, 2), Cells(2, 4).End(xlDown)).Select
which will select row 3 till the end of your table for columns B, C and D

Didn't find what you were looking for? Find more on Linest Function Or get search suggestion and latest updates.