title: VBA: How to return a cell reference from a variable that looksup a value?
body: <p>First of all, I have very little experience with VBA. Here is my problem:</p>
  
  <p>I need to update a certain rate on a daily base in order to perform investments calculations. Right now, I  have a button that once clicked brings the day's rate automatically from a website, and I have to copy and paste the rate to its respective date manually. What I would like to do it to just click the button and the rate would automatically be pasted next to its date. The worksheet looks like this:</p>
  
  <pre><code>                                                Update Button
                                             Day's date        Day's rate
                                             03/01/2013            6%
  
    Date           Rate        
  02/01/2013        5%
  03/01/2013        6%
  04/01/2013
  </code></pre>
  
  <p>The most obvious way to do it would be to use vlookup to look for the the day's date up right and paste it in the correct place at column B, but the problem would be that everytime I update the value, the previous day formula would result in an error. So the best solution would be to use a simple macro that matches the day's date in column A and pastes only the value of the rate next to it. 
  A good strategy to perform this action with normal worksheet functions would be <code>=CELL("address";INDEX(B:B;MATCH($G$5;A:A;0);))</code>, which would result in the cell reference of the right day's rate in column B. The problem is there isn't the =CELL function in VBA.</p>
  
  <p>I tried the following script but it didn't work out:</p>
  
  <pre><code>Sub Teste2()
  
  Dim IndexFormula As Range
  
  MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
  IndexFormula = WorksheetFunction.Index(Range("B:B"), MatchFormula, 0)
  
  Value = IndexFormula.Address
  
  Range("G5").Select
  Selection.Copy
  Value.Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  End Sub
  </code></pre>
  
  <p>What code should I use?</p>
  
  <p>Thanks in advance</p>