title: Excel VBA - Referencing to the cell that the formula is written in
body: <p>I have this simple problem with a custom VBA code that I am trying to code, yet I could not found any solution and it is about to drive me crazy.</p>
  
  <p>I need to have a formula that: </p>
  
  <ul>
  <li>Gets the value of the cell two rows up in the same column, if the current row is 5 or lower OR</li>
  <li>Gets the value of top neighboring cell, if the current row is 6 or greater</li>
  </ul>
  
  <p>In plain English, if the formula is written in cell E5; it should get the value of E3. If the formula is written to cell E6, then it should get the value of E5. Then I'll use that value as input for other formulas, or use it as result, depending on the situation. </p>
  
  <p>Actually I am able to achieve what I want to with following worksheet formula: </p>
  
  <pre><code>IF(ROW()&gt;5; INDIRECT(ADDRESS(ROW()-1; COLUMN())); INDIRECT(ADDRESS(ROW()-2; COLUMN())))
  </code></pre>
  
  <p>If this were to be used alone, It was OK, but this is going to be a part of a bigger nested IF block. If you are interested, the working worksheet formula is looking a bit like this: </p>
  
  <pre><code>=IF(LEFT($C5;2) = "RT"; IF(ROW()&gt;5; INDIRECT(ADDRESS(ROW()-1;COLUMN())); 
  INDIRECT(ADDRESS(ROW()-2; COLUMN())))-$F5; IF(LEFT($C5;2)="DP"; IF(ROW()&gt;5;
  INDIRECT(ADDRESS(ROW()-1;COLUMN())); INDIRECT(ADDRESS(ROW()-2;
  COLUMN())))+$F5; IF(VE($E5=P$4; LEFT($C5;2)="FR"); IF(ROW()&gt;5;
  INDIRECT(ADDRESS(ROW()-1; COLUMN())); INDIRECT(ADDRESS(ROW()-2;
  COLUMN())))-$F5; IF(VE($E5=P$4; LEFT($C5; 2)="RV"); IF(ROW()&gt;5;
  INDIRECT(ADDRESS(ROW()-1; COLUMN())); INDIRECT(ADDRESS(ROW()-2;
  COLUMN())))+$F5; IF(ROW()&gt;5; INDIRECT(ADDRESS(ROW()-1; COLUMN()));
  INDIRECT(ADDRESS(ROW()-2; COLUMN())))))))
  </code></pre>
  
  <p>As you can see, I am looking at a total maintenance nightmare. Even to take a look at it makes me dizzy. So, I decided to move the mentioned bit to a VBA formula, which I thought can be easily written like this: </p>
  
  <pre><code>Function GETTOP()
  
      If (ActiveCell.Row &lt; 6) Then
          GETTOP = CLng(ActiveCell.Offset(-2, 0).Value)
      Else
          GETTOP = CLng(ActiveCell.Offset(-1, 0).Value)
      End If
  
  End Function
  </code></pre>
  
  <p>I know it will raise an error if referenced cell value is not numeric, but I'll deal with that later on. </p>
  
  <p>It works great if I just write formula to the cells directly: </p>
  
  <p><a href="https://i.stack.imgur.com/nklX2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nklX2.png" alt="It works when formula cells edited directly"></a></p>
  
  <p>But when I try to modify the cells which effects the cells with this formula, it tries to reference the cells that I am currently modifying</p>
  
  <p><a href="https://i.stack.imgur.com/rrzxF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rrzxF.png" alt="Not working when affecting cells are modified"></a></p>
  
  <p>since I am using <code>ActiveCell</code> in the function, it naturally tries to refer to the cell which is active. </p>
  
  <p>All the solutions I found in web is using <code>ActiveCell</code> approach, which works great when the cell is edited directly. But when one of the affecting cells are edited, it fails. </p>
  
  <p>So, how can I reference to the actual cell that the formula is written in? </p>