title: How to use address function in Excel VBA?
body: <p>I am trying to use VBA to dynamically add a formula to a few cells using the address function. When I run the following scripts I get a file explorer window that opens. I am not sure why that happens. What am I doing wrong?</p>
  
  <p>Here is one of the solutions I tried:</p>
  
  <pre><code>Sub AddFormulas()
  
  Set countBase = Sheet7.Range("CU2")
  colCount = Sheet7.Range(countBase, countBase.End(xlToRight)).Columns.Count
  
  Dim startCount As Integer
  startCount = 98
  
      For i = 1 To colCount
  
          If IsNumeric(Sheet7.Cells(2, startCount + i)) Then
              Set bSum = Sheet7.Cells(3, colCount + startCount)
              Set bSpr = Sheet6.Cells(3, startCount + i)
  
              Sheet7.Cells(3, i).Formula = "=Sheet6!" &amp; bSpr.Address() &amp; "*" &amp; "Sheet7!" &amp; bSpr.Address()
  
           Else
              'Do some stuff
          End If
      Next i
  End Sub
  </code></pre>
  
  <p>I also tried this:</p>
  
  <pre><code>Sub AddFormulas()
  
  Set countBase = Sheet7.Range("CU2")
  colCount = Sheet7.Range(countBase, countBase.End(xlToRight)).Columns.Count
  
  Dim startCount As Integer
  startCount = 98
  
      For i = 1 To colCount
  
          If IsNumeric(Sheet7.Cells(2, startCount + i)) Then
  
              Sheet7.Cells(3, i).Formula = "=Sheet6!" &amp; Cells(3, startCount + i).Address() &amp; "*" &amp; "Sheet7!" &amp; Cells(3, colCount + startCount).Address() 
  
           Else
              'Do some stuff
          End If
      Next i
  End Sub
  </code></pre>