My syntax error

[daniroy]

Hello there on this code I do receive a syntax error but I cannot
figure out what. Is there something very obvious I am missing ? If you
can, any help will be deeply appreciated !

Best regards to everybody
Daniel


Sub test()

Dim F As String

Spot = Sheets("Implied Dividends").Range("R3").Value
Pricing_Date = Sheets("Implied Dividends").Range("D3").Value

F =
"=BS_Basic(TRUE,Pricing_Date,Sheets("FTSE").Range("F11"),Spot,Sheets("FTSE").Range("G11").Value,Sheets("FTSE").Range("IT11"),Sheets("FTSE").Range("IU11").Value,Sheets("FTSE").Range("IV11").Value,
0"

Sheets("Implied Dividends").Range("E25").Value = F

End Sub

 

[Bob Phillips]

I would guess it should be

F
="=BS_Basic(TRUE,Pricing_Date,Sheets(""FTSE"").Range(""F11""),Spot,Sheets(""
FTSE"").Range(""G11"").Value,Sheets(""FTSE"").Range(""IT11""),Sheets(""FTSE"
").Range(""IU11"").Value,Sheets(""FTSE"").Range(""IV11"").Value,0"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[daniroy]

thanks a lot Bob, I did not think about doubling the speech marks ...
btw I am now jumping into a Run-time error 1004 ... Application-defined
or object error. Help still wellcome! Code is now:
_______________
Sub test()

Dim F As String

Spot = Sheets("Implied Dividends").Range("R3").Value
Pricing_Date = Sheets("Implied Dividends").Range("D3").Value

F =
"=BS_Basic(TRUE,Pricing_Date,Sheets(""FTSE"").Range(""F11""),Spot,Sheets(""FTSE"").Range(""G11"").Value,Sheets(""FTSE"").Range(""IT11""),Sheets(""FTSE"").Range(""IU11"").Value,Sheets(""FTSE"").Range(""IV11"").Value,
0"

Sheets("Implied Dividends").Range("E25").Value = F
_______________

Best regards
Daniel

 

[Bob Phillips]

Daniel,

Didn't look hard enough at what you were doing.

This puts the values in FTSE directly into the formula. Is this what you
want, or do you want the formula to have references in there, so it updates
when they change?

Sub testy()
Dim sFormula As String
Dim F As String
Dim Spot As Double
Dim Pricing_Date As Double

Spot = Sheets("Implied Dividends").Range("R3").Value
Pricing_Date = Sheets("Implied Dividends").Range("D3").Value

sFormula = "=BS_Basic(TRUE," & Pricing_Date & "," &
Sheets("FTSE").Range("F11").Value & "," & Spot & ","
sFormula = sFormula & Sheets("FTSE").Range("G11").Value & "," &
Sheets("FTSE").Range("IT11").Value & ","
sFormula = sFormula & Sheets("FTSE").Range("IU11").Value & "," &
Sheets("FTSE").Range("IV11").Value & ",0)"

Sheets("Implied Dividends").Range("E25").Value = sFormula
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[daniroy]

Thanks again Bob for your help, it is indeed not the first time you
give me hand ! I in facts U did play around and found a way to get the
result I was aiming for. So thanks again and have a good week end if we
dont speak since then ...
regards
Dan

 

[daniroy]

Obviously I mean I did play around, not you !