insert formula using VB - displays formula as string, not result

[Dom Boyce]

Hi,

I have some code which creates a formula within a range of cells,
calling an add-in function (Bloomberg market data). the code is as
follows:

If strRegion = "US" Then
For lngRow = 2 To maxRow

If Cells(lngRow, 1).Value <> "" Then
strCellAddress = Cells(lngRow, 1).Address
Cells(lngRow, 1).Value = Cells(lngRow, 1).Value & " US"
strFormula = "=blp(" & strCellAddress & Chr(38) & Chr(34) & "
Equity" & Chr(34) & "," & Chr(34) & "NAME" & Chr(34) & ")"
Cells(lngRow, 2).FormulaR1C1 = strFormula
End If
Next lngRow
End If


the resulting formula should be something like this:

=blp($A$2&" Equity","NAME")

and indeed it is, but Excel consistently fails to evaluate the
formula, displaying the above within the cell. What am I doing wrong?
I have even tried setting the code to:

strFormula = "=1+2"
but alas "=1+2" is displayed, not "3". Any help greatly appreciated!

Regards

Dom

 

[Niek Otten]

Hi Dom,

Probably your cell is formatted as text.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel