Quotation marks within a formula
That works perfectly, Thank you
"Ben" wrote:
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" wrote:
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"","" "","" "")"
--
Hope that helps.
Vergel Adriano
"Ben" wrote:
=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
|