VBA line

[Guest_imported]

I can't seem to compile the following line, as I have "" within the formula - does any one know how to get round this?

Range("L5&quot.Formula = "=IF(G6="KO","*",IF(AND(G6="RKO",F6="C&quot,ABS(I6-E6)*(K6/I6),IF(G6="OT",K6,IF(AND(G6="RKO",F6="P&quot,ABS(H6-E6)*(K6/H6),IF(AND(G6="RKI",F6="C&quot,ABS(I6-E6)*(K6/I6),IF(G6="OT",L6,IF(AND(G6="RKI",F6="P&quot,ABS(H6-E6)*(K6/H6),0)))))))"

 

[dsi]

Just use double quotes for those that are required in the forumula.

Dim sFormula As String

'Forumla: =IF(A1>0,A1,"")
sFormula = "=IF(A1>0,A1,"""")"
    
Range("B2").Formula = sFormula
[\code]
Hope this helps...

 

[Ed2020]

Try replacing the double quotes in the formula (&quot with single quotes ('). I haven't tried it but I'd imagine that would do the trick.

Ed Metcalfe.

 

[Guest_imported]

I've tried both but no luck, any other suggestions?

 

[dsi]

You could spend the time building the string using Chr(34).

'Forumla: =IF(A1>0,A1,"&quot
sFormula = "=IF(A1>0,A1," & Chr(34) & Chr(34) & &quot"

A little more effort, but it will work.

 

[Navvy]

DSI, I think the problem lies with using " rather than spaces. Because I define certain words in the formula it thinks that it is the end of defining the formula.

 

[dsi]

Chr(34) in my last sample adds quotes to the string literal. You should utilize a string variable to build the string before setting the Forumula property.

Dim sEqn As String

'---------------------------------
'Forumla for Cell: =IF(A1>0,A1,"")
'---------------------------------
sEqn = "=IF(A1>0,A1," & Chr(34) & Chr(34) & ")"

Range("B2").Formula = sEqn

You can also test this by displaying the equation. In the next example, you would build your string and run TestEqn until the displayed value matched the desired forumula.

Sub TestEqn()
  Dim sEqn As String
  sEqn = "=IF(A1>0,A1," & Chr(34) & Chr(34) & ")"
  MsgBox sEqn
End Sub

 

[euskadi]

BenCD... I love to answer questions by not answering them. Without trying to be judgemental, your equation could use some help, is there an easier way to accomplish what you're trying to do?

Or - another thing you might try is to create the equation in Excel on the spreadsheet. Excel's color coding will help you make sure you have your parenthesis right. Then you can record a quick macro adding one small thing like "+1" to the end of the equation and take a look at the VBA that Excel creates.

 

[uberpudge]

I think that dsi is on the right track. The following code put "=IF(G6="KO","*",IF(AND(G6="RKO",F6="C&quot,ABS(I6-E6)*(K6/I6),IF(G6="OT",K6,IF(AND(G6="RKO",F6="P&quot,ABS(H6-E6)*(K6/H6),IF(AND(G6="RKI",F6="C&quot,ABS(I6-E6)*(K6/I6),IF(G6="OT",L6,IF(AND(G6="RKI",F6="P&quot,ABS(H6-E6)*(K6/H6),0)))))))" in cell L5:

Sub Sample()
Dim lQuoteChar As String

lQuoteChar = Strings.Chr(34)

Range("L5&quot.Formula = "=IF(G6=" + lQuoteChar + "KO" + _
lQuoteChar + "," + lQuoteChar + "*" + lQuoteChar + _
",IF(AND(G6=" + lQuoteChar + "RKO" + lQuoteChar + _
",F6=" + lQuoteChar + "C" + lQuoteChar + _
&quot,ABS(I6-E6)*(K6/I6),IF(G6=" + lQuoteChar + "OT" + _
lQuoteChar + ",K6,IF(AND(G6=" + lQuoteChar + "RKO" + _
lQuoteChar + ",F6=" + lQuoteChar + "P" + lQuoteChar + _
&quot,ABS(H6-E6)*(K6/H6),IF(AND(G6=" + lQuoteChar + "RKI" _
+ lQuoteChar + ",F6=" + lQuoteChar + "C" + lQuoteChar + _
&quot,ABS(I6-E6)*(K6/I6),IF(G6=" + lQuoteChar + "OT" + _
lQuoteChar + ",L6,IF(AND(G6=" + lQuoteChar + "RKI" + _
lQuoteChar + ",F6=" + lQuoteChar + "P" + lQuoteChar + _
&quot,ABS(H6-E6)*(K6/H6),0)))))))"

End Sub

Hope this helps,
Pete

 

[euskadi]

I HAVE IT!!!

Do it like this.... drop the equation you want into you cell, replacing the initial "=" with "XXX" and replacing your double quotes with single quotes. Then do a quick find and replace to change the ' to " and the XXX to =. Example below (works!)

Sub workaround()

    Range("C7").Select
    ActiveCell.Value = "xxxIF(G6='KO','*',IF(AND(G6='RKO',F6='C'),ABS(I6-E6)*(K6/I6),IF(G6='OT',K6,IF(AND(G6='RKO',F6='P'),ABS(H6-E6)*(K6/H6),IF(AND(G6='RKI',F6='C'),ABS(I6-E6)*(K6/I6),IF(G6='OT',L6,IF(AND(G6='RKI',F6='P'),ABS(H6-E6)*(K6/H6),0)))))))"

    Cells.Replace What:="'", Replacement:="""", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False
    Cells.Replace What:="XXX", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False
End Sub