-1

I need to set the following formula in the conditional formula using VBA. I tried all different ways like doubling the double quote and Chr(34). Please help.

vFormula = "=OR(TEXT(B$2,"ddd")="Sat",TEXT(B$2,"ddd")="Sun")"
.FormatConditions.Add Type:=xlExpression, Formula1:=vFormula

What is the correct way to pass the Formula1 parameter which contains double quotes?

Henrik Sachse
  • 51,228
  • 7
  • 46
  • 59
naanku
  • 1
  • 1
  • Both methods [should work](http://stackoverflow.com/questions/216616/how-to-create-strings-containing-double-quotes-in-excel-formulas). – wOxxOm Jul 23 '15 at 18:48
  • Try `vFormula = "=OR(TEXT(B$2,""ddd"")=""Sat"",TEXT(B$2,""ddd"")=""Sun"")"`. You need to double up double-quote characters within a quoted string. –  Jul 23 '15 at 18:57
  • 1
    Try `=WEEKDAY(B$2, 2)>5` –  Jul 23 '15 at 18:59
  • why those didn't work for you? have you received error message, or unexpected result? – Máté Juhász Jul 23 '15 at 19:46

1 Answers1

0

This should work for your purposes:

ActiveCell.FormulaR1C1 = _
    "=OR(TEXT(R2C[-3],""ddd"")=""Sat"",TEXT(R2C[-3],""ddd"")=""Sun"")"

You would just have to change the relative cell reference.

Regards,

nbayly
  • 2,167
  • 2
  • 14
  • 23
  • It seems the formula is actually intended as the `Formula1` parameter in the creation of a CF rule but your suggestion would work. xlR1C1 style formulas can be shoveled into the Formula1 parameter. –  Jul 23 '15 at 19:05
  • @nbayly your solution worked for few conditions. However, I have the below condition.
    rFormula = "=AND("POS"=LEFT($A3,3),COUNTIFS($AY$53:$AY$200,"<=" & B$2,$BB$53:$BB$200,">=" & B$2,$BE$53:$BE$200,$A3)=1)"
    rFormula1 = Application.ConvertFormula(rFormula, 1, , 1) gives me
    "=AND("POS"=LEFT(R3C1,3),COUNTIFS(R53C51:R200C51,"<=" & R2C2,R53C54:R200C54,">=" & R2C2,R53C57:R200C57,R3C1)=1)"
    .FormatConditions.Add Type:=xlExpression, Formula1:=rFormula1
    is throwing error     – naanku Aug 25 '15 at 17:57