title: Using cell reference formula in VBA to return cell to use in VBA code
body: <p>I am trying to use a formula with INDEX and MATCH to return a cell reference to enter TEXT into with VBA. 
  I have a list of vendors in column A and to find the cell to the right of it I can use the following </p>
  
  <pre><code>=CELL("address";INDEX(A29:C42;MATCH("***";A29:A42;0);2))
  </code></pre>
  
  <p>However I am struggling with how to get this in my VBA code. (note the value *** is changing as I need to run the sub several times for different vendors.
  Can i use Function sub for this? I have tried as below with no luck:</p>
  
  <pre><code>Sub CellRef()
  '
  ' CellRef
  '
  '
      Function.Range(="CELL("ADDRESS";INDEX(A29:C42;MATCH("Accenture";A29:A42;0);2))")
  
  End Sub 
  </code></pre>