| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax Problem with formula code
Hey guys below is a line of code in my project. In the
VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"")=TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" Thank you Todd Huttenstine |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
Double the double quotes, e.g.:
...AND(B6<"""",E6<"""")=TRUE,... In article , "Todd Huttenstine" wrote: Hey guys below is a line of code in my project. In the VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"")=TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
I doubled quoted everything and now its putting single
quotes around my cell addresses and that is causing my formula to come up with the NAME error. I put this in the VBA code and VBA likes this: ActiveCell.FormulaR1C1 = "=IF(AND(B6<"""",E6<"""") =TRUE,IF(B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF (E6="""",,) & IF(E6<"""",""; ""&E6&"" - 2YR"",),IF (B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF(E6="""",,) & IF(E6<"""",E6&"" - 2YR"",))" However when I run the code it puts the following in the cell: =IF(AND('B6'<"",'E6'<"")=TRUE,IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"","; "&'E6'&" - 2YR",),IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"",'E6'&" - 2YR",)) I need for it to put this formula in the cell instead: =IF(AND(B6<"",E6<"")=TRUE,IF(B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"","; "&E6&" - 2YR",),IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"",E6&" - 2YR",)) -----Original Message----- Double the double quotes, e.g.: ...AND(B6<"""",E6<"""")=TRUE,... In article , "Todd Huttenstine" wrote: Hey guys below is a line of code in my project. In the VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"")=TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF (B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
Todd,
Change ActiveCell.FormulaR1C1 to ActiveCell.Formula B6 is not an RC type address. HTH, Bernie MS Excel MVP "Todd Htutenstine" wrote in message ... I doubled quoted everything and now its putting single quotes around my cell addresses and that is causing my formula to come up with the NAME error. I put this in the VBA code and VBA likes this: ActiveCell.FormulaR1C1 = "=IF(AND(B6<"""",E6<"""") =TRUE,IF(B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF (E6="""",,) & IF(E6<"""",""; ""&E6&"" - 2YR"",),IF (B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF(E6="""",,) & IF(E6<"""",E6&"" - 2YR"",))" However when I run the code it puts the following in the cell: =IF(AND('B6'<"",'E6'<"")=TRUE,IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"","; "&'E6'&" - 2YR",),IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"",'E6'&" - 2YR",)) I need for it to put this formula in the cell instead: =IF(AND(B6<"",E6<"")=TRUE,IF(B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"","; "&E6&" - 2YR",),IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"",E6&" - 2YR",)) -----Original Message----- Double the double quotes, e.g.: ...AND(B6<"""",E6<"""")=TRUE,... In article , "Todd Huttenstine" wrote: Hey guys below is a line of code in my project. In the VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"")=TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF (B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
You can't use A1 references in an R1C1 formula.
Todd Htutenstine wrote: I doubled quoted everything and now its putting single quotes around my cell addresses and that is causing my formula to come up with the NAME error. I put this in the VBA code and VBA likes this: ActiveCell.FormulaR1C1 = "=IF(AND(B6<"""",E6<"""") =TRUE,IF(B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF (E6="""",,) & IF(E6<"""",""; ""&E6&"" - 2YR"",),IF (B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF(E6="""",,) & IF(E6<"""",E6&"" - 2YR"",))" However when I run the code it puts the following in the cell: =IF(AND('B6'<"",'E6'<"")=TRUE,IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"","; "&'E6'&" - 2YR",),IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"",'E6'&" - 2YR",)) I need for it to put this formula in the cell instead: =IF(AND(B6<"",E6<"")=TRUE,IF(B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"","; "&E6&" - 2YR",),IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"",E6&" - 2YR",)) -----Original Message----- Double the double quotes, e.g.: ...AND(B6<"""",E6<"""")=TRUE,... In article , "Todd Huttenstine" wrote: Hey guys below is a line of code in my project. In the VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"")=TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF (B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
Ah that did the trick. Thank you both for your help.
Now what determines when I use the R1C1 option? I have always used it thinking that was just how it was. Todd -----Original Message----- Todd, Change ActiveCell.FormulaR1C1 to ActiveCell.Formula B6 is not an RC type address. HTH, Bernie MS Excel MVP "Todd Htutenstine" wrote in message ... I doubled quoted everything and now its putting single quotes around my cell addresses and that is causing my formula to come up with the NAME error. I put this in the VBA code and VBA likes this: ActiveCell.FormulaR1C1 = "=IF(AND(B6<"""",E6<"""") =TRUE,IF(B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF (E6="""",,) & IF(E6<"""",""; ""&E6&"" - 2YR"",),IF (B6="""",,) & IF(B6<"""",B6&"" - 1YR"",) & IF (E6="""",,) & IF(E6<"""",E6&"" - 2YR"",))" However when I run the code it puts the following in the cell: =IF(AND('B6'<"",'E6'<"")=TRUE,IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"","; "&'E6'&" - 2YR",),IF('B6'="",,) & IF ('B6'<"",'B6'&" - 1YR",) & IF('E6'="",,) & IF ('E6'<"",'E6'&" - 2YR",)) I need for it to put this formula in the cell instead: =IF(AND(B6<"",E6<"")=TRUE,IF(B6="",,) & IF (B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"","; "&E6&" - 2YR",),IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"",E6&" - 2YR",)) -----Original Message----- Double the double quotes, e.g.: ...AND(B6<"""",E6<"""")=TRUE,... In article , "Todd Huttenstine" wrote: Hey guys below is a line of code in my project. In the VBE it is showing up in red because it does not like the "" within the formula. I know the format to enter the formula in the cell is correct because I have used the same format on other occasions. Its just so many of the "" is messing it up. How do I correct this? ActiveCell.FormulaR1C1 = "=IF(AND(B6<"",E6<"") =TRUE,IF (B6="",,) & IF(B6<"",B6&" - 1YR",) & IF(E6="",,) & IF (E6<"","; "&E6&" - 2YR",),IF(B6="",,) & IF (B6<"",B6&" - 1YR",) & IF(E6="",,) & IF(E6<"",E6&" - 2YR",))" . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
Thanks, Bernie - I completely missed the R1C1.
In article , "Bernie Deitrick" <deitbe @ consumer dot org wrote: Todd, Change ActiveCell.FormulaR1C1 to ActiveCell.Formula B6 is not an RC type address. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Problem with formula code
It's sometimes easier to use R1C1 when you're using relative references.
In article , "Todd Huttenstine" wrote: Now what determines when I use the R1C1 option? I have always used it thinking that was just how it was. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Syntax problem with code | Excel Discussion (Misc queries) | |||
| VBA code to sum a row: syntax needed | Excel Discussion (Misc queries) | |||
| syntax question - cell references in VB code | New Users to Excel | |||
| help with code/syntax | Excel Programming | |||
| Change hard code to Variable syntax | Excel Programming | |||
Linear Mode
