title: Excel VBA - Loop through range and set formula in each cell
body: <p>I've got a workbook where I have one worksheet which contains a lot of data. 
  My goal is to create a macro that inserts a formula in a separate sheet to copy the data from the first sheet. Lets call the first sheet "Numbers1" and the second sheet "TidyNumbers1".</p>
  
  <p>In the sheet "TidyNumbers1" I want to loop through each cell from column A to M and rows 1 to 60. So I've got a macro that so far looks like this:</p>
  
  <pre><code>   Sub updateFormulasForNamedRange()
      Dim row, col, fieldCount As Integer
      colCount = 13
      RowCount = 60
  
      For col = 1 To colCount
          For row = 1 To RowCount
              Dim strColCharacter
  
              If col &gt; 26 Then
                  strColCharacter = Chr(Int((row - 1) / 26) + 64) &amp; Chr(((row - 1) Mod 26) + 65)
              Else
                  strColCharacter = Chr(row + 64)
              End If
  
              Worksheets("TidyNumbers1").Cells(row, col).Formula = "=IF(Numbers1!E" &amp; col &amp; "&lt;&gt;0;Numbers1!" &amp; strColCharacter &amp; row &amp; ";"")"
          Next row
      Next col
  
  End Sub
  </code></pre>
  
  <p>But the formula is supposed to looks like this for Column A, row 2:</p>
  
  <pre><code>IF(Numbers1!E2&lt;&gt;0;Numbers1!A2;"")"
  </code></pre>
  
  <p>And the formula in Column A, row 3 should look like this:</p>
  
  <pre><code>IF(Numbers1!E3&lt;&gt;0;Numbers1!A3;"")"
  </code></pre>
  
  <p>Formula in Column B, row 2 should look like this:</p>
  
  <pre><code>IF(Numbers1!E2&lt;&gt;0;Numbers1!B2;"")"
  </code></pre>
  
  <p>In other words, the formula looks to see if the value in Column E, row % is anything but 0 and copies it if conditions are met. </p>
  
  <p>But, I see that I need to translate my integer variable Row with letters, because the formula probably needs "A" instead of 1. Also, I get a 1004 error (Application-defined or object-defined error) if I just try to use:</p>
  
  <pre><code>Worksheets("Numbers1").Cells(row, col).Formula = "=IF(Numbers1!E" &amp; row &amp; "&lt;&gt;0;Numbers1!" &amp; col &amp; row &amp; ";"")"
  </code></pre>
  
  <p>I clearly see that the integer row should be translated to letters, if that's possible. Or if anyone has any other suggestions that might work. Also, the 1004 error is unclear to me why happens. I can define a string variable and set the exact same value to it, and there's no error. So it's probably the formula bar that whines about it I guess?</p>