How to insert a formula in a cell using vba variables

hope someone here is able to help me: I'm trying to calculate the sample covariance out of a long data set i have on one worksheet, using info i have in a second worksheet to select each sample. the first worksheet that contains the data set, named "C1% Norm" looks like this:

          |  A  |  B  |  C  |  D  |  E  |  F  |
       1  |throw|temp |depth|  s  |  c  | sp  |
       2    60    0,3  0,456 0,123  ...   ...  
       ..   ...   ...   ...   ...   ...   ...
      189   61   0,42   ...   ...   ...   ...
       ..   ...   ...   ...   ...   ...   ...
      375   62   0,35   ...   ...   ...   ...
       ..

the second worksheet contains the limits of each "throw" or sample to be used, named "Mapeo Lances" and looks like this:

          |  A  |  B  |  C  | 
       1  |throw|lim i|lim s|
       2    60     2    188
       3    61    189   374
       4    62    375   ... 

So essentially what needs to be done, is to calculate the sample covariance of for example "temp" and "depth" using the data between the rows 2 and 188 (of the first worksheet) as a sample, then using the data from rows 189-374 as a sample and so on...

i wrote this code:

    Sub covariances()

    Dim i As Integer
    Dim limi As Integer
    Dim lims As Integer
    Dim test As String

    i = 2
    Sheets("Mapeo Lances").Select

    While i < Cells(Rows.count, 1).End(xlUp).Row + 1

    limi = Range("B" & i).Value
    lims = Range("C" & i).Value

    test = "=covariance.s('C1% Norm'!B" & limi & ":B" & lims & ";'C1% Norm'!C" & limi & ":C" & lims & ")"
    Range("D" & i).Formula = test
    i = i + 1

    Wend

    End Sub

but i get the following error: Runtime Error : '1004' application-defined or object-defined error

I know the "test" string is being concatenated correctly; if i remove the "=" sign at the beginning of the string, the cell gets filled with the desired text with no errors.

I know i can get it to work using this:

   ...
   dim range_i as Range
   dim range_s as Range

   ...
   while...
   ...
   set range_i = Worksheet("C1% Norm").Range("B" & limi & ":B" & lims)
   set range_s = Worksheet("C1% Norm").Range("C" & limi & ":C" & lims) 

   Range("D" & i).value = Application.WorksheetFunction.Covariance_S(range_i,range_s)

   wend  

But I actually need the formula in each cell for teaching purposes... what am i doing wrong?