title: Can an array be used within the LinEst function in VBA?
body: <p>Basically, rather that selecting a range from cells I have stored values in an array through the use of a loop. What I would ideally like to do is use these arrays as the known x's and y's in the LinEst function.</p>
  
  <p>The purpose of this is unimportant as what I am trying to do is only part of the code I have already written. However, the Do loops (well at least the 2nd) do need to be there as the code I am attempting to apply this to requires them in order to function. </p>
  
  <p>Below is a simple example of code I am trying to write.</p>
  
  <pre><code>Sub Test()
  
  Dim Counter As Long
  Dim Counter_1 As Long
  
  Dim x As Single
  Dim y As Single
  Dim i As Single
  Dim m As Single
  
  Dim myArray_1() As Single
  Dim myArray_2() As Single
  
  ReDim myArray_1(i)
  ReDim myArray_2(i)
  
  Counter = 2
  Counter_1 = 2
  
  i = 0
  
  Cells(1, 4) = "m"
  
  x = Cells(Counter, 1)
  y = Cells(Counter, 2)
  
  Do
  
  Do Until x = 0
  
  myArray_1(i) = x
  myArray_2(i) = y
  
  Cells(Counter, 6) = myArray_1(i)
  Cells(Counter, 7) = myArray_2(i)
  
  
  i = i + 1
  
  Counter = Counter + 1
  
  x = Cells(Counter, 1)
  y = Cells(Counter, 2)
  
  ReDim Preserve myArray_1(i)
  ReDim Preserve myArray_2(i)
  
  Loop
  
  m = WorksheetFunction.LinEst(myArray_2, myArray_1)
  
  Cells(Counter_1, 4) = m
  
  Loop
  
  End Sub
  </code></pre>
  
  <p>So basically I want the LinEst function to use each array as known y's and known x's. Depending on what I change I get different errors, such as "type mismatch", or "unable to get the LinEst property of the worksheetfunction class". Either way I have so far had no luck in getting this to work and it always errors. From the LinEst function all I want is the gradient 'm'.</p>
  
  <p>The only reason things have been put into cells is to make sure that the code is doing what I ask of it.</p>
  
  <p>From what I can tell looking around the internet it is possible to use an array within the LinEst function, however the examples are usually drastically different to what I am trying to do.</p>
  
  <p>If anyone can help at all I would be most greatful. Thank you in advance. Any questions feel free to ask.</p>