title: Copying custom function to other cells (changing active cell)
body: <p>I am trying to make this custom function work when I copy in a number of cells. The function should fill in the cells between two numbers with a lineer increase.</p>
  
  <pre><code>Function HINT(FirstCell, LastCell)
  
  x = FirstCell.Column
  y = LastCell.Column
  a = FirstCell
  b = LastCell
  step = (b - a) / Abs(x - y)
  Actcellc = ActiveCell.Column
  HINT = FirstCell + (Actcellc - x) * step
  End Function
  </code></pre>
  
  <p>It works if I copy it to each cell between firstcell and lastcell one by one but doesn't work if I just select the range inbetween and copy (Just calculates the same number in each cell because it still sees the active cell as the origin of copy. If I copy one by one the active cell changes and function works correctly). I can understand that I need to change the activecell definition to another thing but I just don't know what. </p>
  
  <p>Thanks in advance.</p>
  
  <p>EDIT:</p>
  
  <p>I will answer my own question with the helps of others:</p>
  
  <pre><code>Function HINT(FirstCell, LastCell, pos As Range)
  
  Dim i As Long
  Dim res() As Double
  Dim rng As Range
  
  x = FirstCell.Column
  y = LastCell.Column
  a = FirstCell
  b = LastCell
  step = (b - a) / Abs(x - y)
  
  HINT = FirstCell + (pos.Column - x) * step
  
  End Function
  </code></pre>
  
  <p>In the first blank cell (in this case C2) the code should be entered like this:
  =hint($B$2,$N$2,C2)</p>
  
  <p>$B$2 First value, fixed / 
  $N$2 Last value, fixed  / 
  C2 for position, first blank cell to be filled.</p>
  
  <p>Thanks everybody.</p>