title: Excel VBA: inserting WHEN formula into specific range of cells with variable changing column
body: <p>Hello dear StackOverflow users,</p>
  
  <p>My issue is regarding inserting a Formular via Macro into specific range of cells. Again, again and again.</p>
  
  <p>So the Basic function of the formula should be the following:</p>
  
  <p><code>=WHEN('ZEK '!F3="X";"6";WHEN('ZEK '!G3="X";"8";"1"))</code></p>
  
  <p>In my macro it has to Change the column for the next specific range.
  This is Happening in a for Loop for <code>i</code> growing.</p>
  
  <p>meaning --> <code>=When('ZEK '!$F$"&amp; i+2 &amp;".......</code>
  or in any other Syntax that should work. My try fairly does not, thats why I need your help.</p>
  
  <p>This is my Initial try where i exchanged literally everything with strings:</p>
  
  <pre><code>Sub 123()
  Dim a,b,c As String
  a = 1
  b = 6
  c = 8
  
  ....
  
  'used and declared variables k, x, f, a, b, c
  
  Range(Cells(k + 2, 5), Cells(k + 27, 5)).FormulaR1C1 = _
  "=WHEN('ZEK'!$F$" &amp; f &amp; "=" &amp; x &amp; ";" &amp; a &amp; ";WHEN('ZEK'!$G$" &amp; f &amp; "=" &amp; x &amp; ";" &amp; b &amp; ";" &amp; c &amp; "))"
  
  End Sub
  </code></pre>
  
  <p>With that i get Runtime Error 1004. (changed it to .Formula from .FormulaR1C1)</p>
  
  <p>I hope i gave enough Information, so that you could help me.
  This really does not have to be performant, i just Need to get the formula into about 100.000 cells with <code>i</code> changing for each Range of cells</p>