Problems in VBA with Bloomberg function on a LOOP

I'm trying to use this code to introduce a formula from A2 to the end of my database in steps of two cells.

Sub addbdh()

Dim i As Integer
Dim n As Integer

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select

n = Selection.Count

For i = 1 To n

Cells(3, i * 2 - 1).Formula = "BDH(""A"" & "i * 2 - 1", "A1", "B1, "hoy()")"

Next i

End Sub

The problem is that I get an error on the instruction:

Cells(3, i * 2 - 1).Formula = "BDH(""A"" & "i * 2 - 1", ""A1"", ""B1"", "Today()")"

specifically in the "i * 2 - 1" part, I get an Excel MsgBox which says "Expected end of statement" (in spanish: "se esperaba: fin de la instrucción".

¿Could anyone help me to find the error on the code?

Tank you so much.

Solution

In general, .Formula shows the real English formula. Whatever your language is. Thus, Hoy() should be changed to Today().

In general, try like this:

Cells(3, i * 2 - 1).Formula = "=BDH(A" & i * 2 - 1 & ", A1, B1, Today())"

In general, when you have a problem like "How to translate a working Excel formula to VBA" do the following:

Select the working formula in Excel.
Run the following code.
Take the formula from the immediate window and fix it a bit to put the variable i * 2 - 1.

Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub