CODEWITHSUNDEEP
excelvba
Patux
Patux

Reputation: 37

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?

Upvotes: 2

Views: 238

Answers (1)

A.S.H
A.S.H

Reputation: 29332

So you have the ; (semi-colon) as List Separator in your regional settings. This works fine for Excel formulas but unfortunately, not when setting the formula from VBA. When setting it from VBA, use comma:

test = "=covariance.s('C1% Norm'!B" & limi & ":B" & lims & ",'C1% Norm'!C" & limi & ":C" & lims & ")"
'                                                          ^^^

Then by magic, you will find that comma replaced by ; in Excel. It seems that the philosophy of the regional settings feature targets end users, not (VBA) programmers.

You can alternatively use .FormulaLocal to set the formula in VBA to exactly how it looks in Excel:

Range("D" & i).FormulaLocal = test

Upvotes: 2

Related Questions