title: Excel VBA - Name.RefersTo formula proper address specification
body: <p>I have a formula in the cell, let's call it Source Cell, which is linked to other cells in the sheet. 
  My goal is to create the named range which would have this formula saved as a RefersTo property of that name. </p>
  
  <p>When I add new name like this</p>
  
  <p><code>wbk.Names.Add Name:=NameTitle, RefersTo:=Cell.Formula</code></p>
  
  <p>the new name refers to the source cell as A1 and therefore all linked cells are offset by the number of rows and columns of source cell</p>
  
  <p>I thought a while and tried RefersToLocal - didn't help. Than I tried ReferstoR1C1 - that helped, but only if I select one cell. When I select the range of several cells and loop through each cell - it still creates names with a formula from the first cell.</p>
  
  <p>I understand it's due to the way ranges and address are specified but am I doing something wrong and how to fix it? Any ideas? File attached.</p>
  
  <p>My code </p>
  
  <pre><code>Sub TestNameCreation()
  Dim Cell As Range, CellFormula As String, strName As String, nme As Name
  Dim rng As Range, msg As String
  
  Set rng = Range(ActiveWindow.Selection.Address)
  For Each Cell In rng.Cells
  
      CellFormula = Cell.FormulaR1C1
      strName = "Test" &amp; Format(Now(), "hhmmss")
      ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:=CellFormula
      Set nme = Names(strName)
      msg = msg &amp; "Cell: " &amp; Cell.Address &amp; vbCrLf &amp; "Name: " &amp; _
          nme.Name &amp; vbCrLf &amp; "RefsTo: " &amp; nme.RefersTo &amp; vbCrLf &amp; vbCrLf
  Next Cell
  
  MsgBox msg
  </code></pre>
  
  <p>End Sub`</p>
  
  <p><a href="https://i.stack.imgur.com/OTNXF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OTNXF.png" alt="enter image description here"></a></p>