在整个列中放置带if语句的公式

Question

我用下面的公式记录了一个宏，但在第二行中给出了一个错误。

预期报表结束。

我想问题是这条路要走很长的路。请给我建议一下该怎么做？

亚Macro1()

    Range("CG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC69=""High"",(IF(AND(RC4<>"""",RC5<>"""",RC6<>"""",RC7<>"""",RC8<>"""",RC10<>"""",RC11<>"""",RC12<>"""",RC13<>"""",RC14<>"""",RC16<>"""",RC17<>"""",RC18<>"""",RC19<>"""",RC20<>"""",RC21<>"""",RC22<>"""",RC23<>"""",RC24<>"""",RC25<>"""",RC26<>"""",RC27<>"""",RC28<>"""",RC29<>"""",RC30<>"""",RC31<>"""",RC32<>"""",RC33<>"""",RC34<>"""",RC35<>"""",RC36<>"""",RC37<>"""",RC38<>"""",RC39<>"""",RC40<>"""",RC41<>"""",RC42<>"""",RC43<>"""",RC44<>"""",RC45<>"""",RC46<>""""),""No"",""Yes""))," & Chr(10) & "(IF(RC69=""Medium"",(IF(AND(RC4<>"""",RC5<>"""",RC6<>"""",RC7<>"""",RC8<>"""",RC10<>"""",RC11<>"""",RC12<>"""",RC13<>"""",RC14<>"""",RC16<>"""",RC17<>"""",RC18<>"""",RC19<>"""",RC20<>"""",RC21<>"""",RC22<>"""",RC23<>"""",RC31<>"""",RC32<>"""",RC33<>"""",RC34<>"""",RC35<>"""",RC36<>"""",RC37<>"""",RC38<>"""",RC39<>"""",RC40<>"""",RC41<>"""",RC42<>"""",RC44<>"""",RC45<>"""",RC46<>""""),""No"",""Yes""))," & Chr(10) & "(IF(AND(RC4<>"""",RC5<>"""",RC6<>"""",RC7<>"""",RC8<>"""",RC10<>"""",RC11<>"""",RC12<>""""
""",RC14<>"""",RC16<>"""",RC17<>"""",RC18<>"""",RC19<>"""",RC20<>"""",RC31<>"""",RC32<>"""",RC33<>"""",RC34<>"""",RC35<>"""",RC36<>"""",RC37<>"""",RC38<>"""",RC39<>"""",RC41<>"""",RC42<>"""",RC45<>"""",RC46<>""""),""No"",""Yes"")))))"

End Sub

Answer 1

这里的问题是倍数"，您也有一种不同的类型:不工作的“！

所以将"替换为Chr(34)，如下所示：

Sub formula()
Range("CO2:CO").formula = "=IF(OR(LEN($BN2)=0;$BN2=" & Chr(34) & "Not Performed" & Chr(34) & _
            ";LEN($BK2)=0;$BK2=" & Chr(34) & "Not Performed" & Chr(34) & _
            ";LEN($BL2)=0;$Q2=" & Chr(34) & "Not Performed" & Chr(34) & _
            ";LEN($BM2)=0);" & Chr(34) & "Yes" & Chr(34) & ";" & Chr(34) & _
            "No" & Chr(34) & ")"
End Sub