title: Excel VBA iterate over all used cells and substitute their values
body: <p>This is my first time ever working with VBA and Excel and my problem is probably caused by a big lack of knowledge on my part. </p>
  
  <p>I have a one column list of city names (<em>containing 800 items, hence I'm looking to automatically replace them</em>) and some chunks of text in which the term "cityname" occurs multiple times and needs to be replaced by the correct city name, which in my setup would be the <strong>value of the first column cell in the same row</strong>.</p>
  
  <p>So I found this: <a href="https://stackoverflow.com/questions/73785/how-to-iterate-through-all-the-cells-in-excel-vba-or-vsto-2005">How to iterate through all the cells in Excel VBA or VSTO 2005</a></p>
  
  <p>and found the <strong>InStr</strong> and <strong>Substitute</strong> functions by looking through the Excel VBA reference.</p>
  
  <p>Using the iteration like this works fine:</p>
  
  <pre><code>Sub MySubstitute()
      Dim cell As Range
          For Each cell In ActiveSheet.UsedRange.Cells
              If InStr(cell.Value, "cityname") &gt; 0 Then
                  MsgBox "Hello World!"
          End If
      Next cell
  End Sub
  </code></pre>
  
  <p>I get a message box for every "cityname" in my sheet (<em>a test sheet with only 2 rows</em>). </p>
  
  <p>However when I add what I really want to achieve I get a <strong>Runtime Error (1004)</strong>:</p>
  
  <pre><code>Sub MySubstitute()
      Dim cell As Range
      For Each cell In ActiveSheet.UsedRange.Cells
          If InStr(cell.Value, "cityname") &gt; 0 Then
              cell.Value = WorksheetFunction.Substitute(cell.Value, "cityname", Cells(cell.Row, A))
          End If
      Next cell
  End Sub
  </code></pre>
  
  <p>So I guess something in that line is wrong but I can't figure out what. Any hints to what my mistake is are appreciated, thanks :)</p>