CODEWITHSUNDEEP
vba
Aqua
Aqua

Reputation: 11

Breaking up R1C1 formula in vba

I am working on an R1C1 formula that needs multiple "if" conditions. In order to make it easy to read and debug, I want to break it up into different lines. I am using " & _ in first line and then start the next line with ". However, I think I am missing something because I get compile error saying Expected: List separator or ). Below is the code I am working on. Any help with this would be much appreciated.

Selection.FormulaR1C1 = IFERROR(" & _
"IF(RC5>=0.75," & _
"IF(AND(RC16=0,RC20=0),""No Action Req: None of the Predictors have an Estimate""," & _
"IF(AND(RC16=0,RC20<>0,RC8>RC9),""Less Similar Comp Retained: Check if P1 is important, no action req. otherwise""," & _
"IF(AND(RC16<>0,RC20=0,RC9>RC8),""Less Similar Comp Retained: Check if P2 is important, no action req. otherwise""," & _
"IF(AND(RC16=0,RC20<>0,RC8<RC9),""No Action Req: Model is retaining more similar comp.""," & _
"IF(AND(RC16<>0,RC20=0,RC9<RC8),""No Action Req: Model is retaining more similar comp.""," & _
"IF(AND(RC16<>0,RC20<>0,RC8>=70%,RC9>=70%),""Keep Both: Both similar comps""," & _
"IF(AND(RC16<>0,RC20<>0)," & _
"if(abs(RC8-RC9)<=5," & _
    "if((RC8/RC6)<(RC9/RC7),""Drop P1: P1 wins over P2 wrt score and count"",""Drop P2: P2 wins over P1 wrt score and count"")," & _
"IF(AND(RC16<>0,RC20<>0,RC8<RC9),""Drop P1: P2 is more similar to TGT""," & _
"IF(AND(RC16<>0,RC20<>0,RC9<RC8),""Drop P2: P1 is more similar to TGT""," & _
"""""))))))))))),"""")

Upvotes: 0

Views: 295

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You're missing a double quote here:

Selection.FormulaR1C1 = IFERROR(" & _

Should be (note the opening =):

Selection.FormulaR1C1 = "=IFERROR(" & _

And then, every string literal in the formula needs to have its double quotes escaped (i.e. doubled-up).

This code compiles and correctly sets the formula:

Selection.FormulaR1C1 = "=IFERROR(" & _
"IF(RC5>=0.75," & _
"IF(AND(RC16=0,RC20=0),""No Action Req: None of the Predictors have an Estimate""," & _
"IF(AND(RC16=0,RC20<>0,RC8>RC9),""Less Similar Comp Retained: Check if P1 is important, no action req. otherwise""," & _
"IF(AND(RC16<>0,RC20=0,RC9>RC8),""Less Similar Comp Retained: Check if P2 is important, no action req. otherwise""," & _
"IF(AND(RC16=0,RC20<>0,RC8<RC9),""No Action Req: Model is retaining more similar comp.""," & _
"IF(AND(RC16<>0,RC20=0,RC9<RC8),""No Action Req: Model is retaining more similar comp.""," & _
"IF(AND(RC16<>0,RC20<>0,RC8>=70%,RC9>=70%),""Keep Both: Both similar comps""," & _
"IF(AND(RC16<>0,RC20<>0)," & _
"if(abs(RC8-RC9)<=5," & _
    "if((RC8/RC6)<(RC9/RC7),""Drop P1: P1 wins over P2 wrt score and count"",""Drop P2: P2 wins over P1 wrt score and count"")," & _
"IF(AND(RC16<>0,RC20<>0,RC8<RC9),""Drop P1: P2 is more similar to TGT""," & _
"IF(AND(RC16<>0,RC20<>0,RC9<RC8),""Drop P2: P1 is more similar to TGT""," & _
"""""))))))))))),"""")"

However I don't have sample data and the formula evaluates to FALSE for me, which means something is probably wrong with the nested conditional logic itself - and to be honest I wouldn't want to debug this kind of formula.

Working against Selection is dangerous. Seriously consider working against a specific Range object, if that's what you mean to be doing - here's a trivial example (which may or may not be what you need):

Dim targetCell As Range
Set targetCell = Sheet1.Range("A1")

targetCell.FormulaR1C1 = ...

The real solution IMO, would be to drop the idea of evaluating that cell with a formula, and instead implement the conditional logic in actual VBA code. You'll need to Set a Range object reference representing RC5, RC16, and every other cell you're using (give them meaningful names). The result might look something like this:

Dim result As String
If WhateverRC5is.Value >= 0.75 Then
    If WhateverRC16Is.Value = 0 And WhateverRC20is.Value = 0 Then
        result = "No Action Req: None of the Predictors have an Estimate"
    ElseIf WhateverRC16Is.Value = 0 And WhateverRC20is.Value <> 0 And WhateverRC8is.Value > WhateverRC9is.Value Then
        result = "Less similar comp retained: check if P1 is important.."
    ElseIf ...
    ...
    End If
Else
    '...
End If
targetCell.Value = result

Having actual VBA code, with proper indentation and compile-time validation, will be MUCH easier to maintain and debug than whatever layout you can come up with to make that fiendish string digestible.

Good luck!

Upvotes: 1

Related Questions