Syntax Problem with formula code

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

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
 
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
 
Todd,

Change

ActiveCell.FormulaR1C1

to

ActiveCell.Formula

B6 is not an RC type address.

HTH,
Bernie
MS Excel MVP
 
You can't use A1 references in an R1C1 formula.

Todd said:
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",))
Click to expand...
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check Box 3
Formula 2
Conditional Formating: MAX & MIN 3
Speed up calcs within macro.... 7
summing values according to name in defferent column 4
Chart should read as blank cell not as 0,it shows blank but reads 1
Removal of Zero 4
sum of blank cells returns zeros 7

Back
Top