Quotation marks within a formula

[Ben]

=BLPH(A1,B2,"28/2/2001","17/4/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")

I am trying to get a macro to input this formula in cell A1.
I've used the following line of code:

Range("A1").Formula=
"=BLPH(A1,B2,"28/02/2001","17/04/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")"

Unfortunately the editor does not allow this to be input as a line of code I
get an error message saying Compile error. Expected :End of statement. I'm
sure that it has to do with the quotation marks within the formula. What is
the correct syntax for this please.
Thank you

 

[Vergel Adriano]

Ben,

you'll need to double the quotation marks that are inside your string...
like this:

Range("A1").Formula =
"=BLPH(A1,B2,""28/2/2001"",""17/4/2007"",0,FALSE,""D"",""N"","""",TRUE,1600,2,FALSE,""P"","" "","" "")"

 

[Ben]

I have amended the macro as follows

Cell D1 contains 28/2/02 formatted as a date
Cell E1 contains the formula Text(D1,"dd/mm/yyyy") and it correctly shows
28/02/2001 as a string

Sub EnterFormula()
Start_date = Range("E1")
Range("A3").Formula = "=BLPH(A1,B2," & Start_date &
",""17/04/2007"",0,FALSE,""D"",""N"","" "",TRUE,1600,2,FALSE,""P"","" "",""
"")"
End Sub

The resulting formula in A3 appears as follows

=BLPH(A1,B2,28/2/2001,"17/04/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")

This is mostly correct except that the first date appears as 28/2/2001
instead of 28/02/2001

Thes second problem is that my objective is to surround the first date with
quotation marks like the second date in the formula.

Howver if I amend the code as follows

Sub EnterFormula()
Start_date = Range("E1")
Range("A3").Formula = "=BLPH(A1,B2," & """ & Start_date & """ &
",""17/04/2007"",0,FALSE,""D"",""N"","" "",TRUE,1600,2,FALSE,""P"","" "",""
"")"
End Sub

The result produced in A3 is a as follows

=BLPH(A1,B2," & Start_date & ","17/04/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")

This is not what I'm trying to achieve. My objective is for the value of
Start_date to appear as 28/02/2001 and it should be surrounded by quotation
marks as in the second date. How can I achieve this.

Thank you

 

[Vergel Adriano]

Ben,

Try it this way:

Sub EnterFormula()
Dim Start_date As String
Start_date = Range("E1").Text
Range("A3").Formula = "=BLPH(A1,B2,""" & Start_date &
""",""17/04/2007"",0,FALSE,""D"",""N"","" "",TRUE,1600,2,FALSE,""P"","" "",""
"")"
End Sub

 

[Ben]

That works perfectly, Thank you

I have amended the macro as follows

Cell D1 contains 28/2/02 formatted as a date
Cell E1 contains the formula Text(D1,"dd/mm/yyyy") and it correctly shows
28/02/2001 as a string

Sub EnterFormula()
Start_date = Range("E1")
Range("A3").Formula = "=BLPH(A1,B2," & Start_date &
",""17/04/2007"",0,FALSE,""D"",""N"","" "",TRUE,1600,2,FALSE,""P"","" "",""
"")"
End Sub

The resulting formula in A3 appears as follows

=BLPH(A1,B2,28/2/2001,"17/04/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")

This is mostly correct except that the first date appears as 28/2/2001
instead of 28/02/2001

Thes second problem is that my objective is to surround the first date with
quotation marks like the second date in the formula.

Howver if I amend the code as follows

Sub EnterFormula()
Start_date = Range("E1")
Range("A3").Formula = "=BLPH(A1,B2," & """ & Start_date & """ &
",""17/04/2007"",0,FALSE,""D"",""N"","" "",TRUE,1600,2,FALSE,""P"","" "",""
"")"
End Sub

The result produced in A3 is a as follows

=BLPH(A1,B2," & Start_date & ","17/04/2007",0,FALSE,"D","N","
",TRUE,1600,2,FALSE,"P"," "," ")

This is not what I'm trying to achieve. My objective is for the value of
Start_date to appear as 28/02/2001 and it should be surrounded by quotation
marks as in the second date. How can I achieve this.

Thank you