title: Placing variables inside formulas that require cell reference
body: <p>I am attempting to create a macro that creates X amount of named formulas that use the offset formula.  The X will represent the number of data series (columns) that are on sheet1. This is how I want this to look for one of them to be created.</p>
  
  <pre><code>formula = offset($b$2,$a$2,0,$a$3,1)
  activeworkbook.names.add Name:="test", Refers to:=formula
  </code></pre>
  
  <p>But I'm going to use a do loop until i = # of columns with values.  So this is my best shot at this, and i'm sure there are plenty of errors because i can't make it run:</p>
  
  <pre><code>columns = activesheet.countif((1:1),"*"
  i = 5 
  do until i = columns
  
  'This is where i want to define my static cells location (not value) to be referenced in the formula
      pan = cells(1,2)
      zoom = cells(1,3)
  
  'This is where i want to define my dynamic cells location (not value) based on the current column
      series = cells(2,i)
  
  'This is where i want to create a name formulas specific to each series
      formula = "=offset(" &amp; series &amp; "," &amp; pan &amp; ",0" &amp; zoom &amp; ",1)
      activeworkbook.names.add name:=cells(1,i), refersto:=formula
  </code></pre>
  
  <p>So my results are basically, I get a new name formula but the reference formula is filled with integers and not the actual cell address. This is because I'm using a formula that calls the cell's value rather than pasting the cells address.</p>
  
  <p>Any help would be great.</p>