title: excel vba: can't set cells formula from a macro
body: <p>I'm trying to set a formula using a macro. I've come to a point where my macro creates a string with formula that I want to insert but I get an error when I try to set the formula. Here's the code:</p>
  
  <pre><code>Option Explicit
  
  Function AutoFormula(blocks As Range, target As Range)
      Dim blockedArr() As String
      Dim cellValue As String
      Dim ret As String
      Dim i As Integer
  
      'sprawdzenie zakresu
      If (blocks.Cells.Count &gt; 1) Then
          AutoFormula = "Tylko 1 komórka jako parametr!"
          Exit Function
      End If
  
       'wczytanie komorki
      cellValue = blocks.Cells(1, 1).Value
  
      'split
      blockedArr = Split(cellValue, ",")
  
      ret = "=WYSZUKAJ.PIONOWO(E7;$E$2:$G$5;3;FAŁSZ)"
  
      For i = LBound(blockedArr, 1) To UBound(blockedArr, 1)
          ret = ret + "+SUMA.JEŻELI(A7:A1000;" + blockedArr(i) + ";G7:G1000)"
      Next i
  
      target.Cells(1, 1).Value = ret
  End Function
  
  Sub auto()
      Call AutoFormula(Worksheets("reorganizacja").Range("D7"), Worksheets("reorganizacja").Range("G7"))
  End Sub
  </code></pre>
  
  <p>I get a </p>
  
  <blockquote>
    <p>Runtime error 1004: Application-defined or object-defined error</p>
  </blockquote>
  
  <p>in the last line of AutoFormula function. I have no idea what's that supposed to mean except that 'there is some error related to something'.
  Anyways, I also tried using <code>target.Formula = ret</code> but it's the same case.</p>