| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quotation marks within a formula
=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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quotation marks within a formula
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quotation marks within a formula
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quotation marks within a formula
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 -- Hope that helps. Vergel Adriano "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| When do I use quotation marks in an Excel formula? | Excel Worksheet Functions | |||
| Literal quotation marks retained from a formula | Excel Discussion (Misc queries) | |||
| formula strings containing quotation marks - VBA | Excel Programming | |||
| Use quotation marks in a formula to display a qty in feet and inc. | Excel Worksheet Functions | |||
| Using quotation marks | Excel Programming | |||
Linear Mode
