使用Excel VBA插入公式 [英] Inserting a formula using Excel VBA

问题描述

我需要在使用vba编码时插入公式。

I am needing to insert a formula in using vba coding.

代码行

=IF(
    AND(
        Compliance="No",
        OR(
            Delivery <> "",
            C31 <> ""
        )
    ),
    "Please Delete Compliance Data",
    IF(
        AND(
            E11="",
            E13="",
            E23="",
            E25="",
            E26="",
            E28="",
            E30="",
            E31=""),
        VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),
        "Please complete form / SC request"
    )
)"

似乎在文本上出错

任何帮助将不胜感激。

推荐答案

以下是VBA中字符串内引用引号的四种方法：

Here are four ways to use quotation marks inside strings in VBA:

• 替换与Chr $（34）。 34是用于引号的ASCII代码


• 使用String（2，Chr $（34））进行双引号


• 使用两个双引号对于每一个（每个Tim的评论）


• 用不同的方法替换两个双引号，如ISBLANK的工作表函数。

• Replace "" with Chr$(34). 34 is the ASCII code for quotation marks
• Use String(2,Chr$(34)) to make double quotes
• Use two double quotation marks for every one (per Tim's comment)
• Replace two double quotation marks with a different method, like ISBLANK for worksheet functions.

这些都不是更好。我通常用两个双引号来逃避他们。但是，有时我会一起得到这么多的引号，很难阅读和维护，所以我会用不同的方法。以下是一个例子：

None of these is better. I usually use two double quotation marks to escape them. But sometimes I get so many quotation marks together that it's hard to read and maintain, so I'll use a different method. Here's an example:

Dim aFormula(1 To 5) As String

aFormula(1) = "=IF(AND(Compliance=""No"",OR(Delivery<>" & String(2, Chr$(34))
aFormula(2) = ",C31<>" & String(2, Chr$(34)) & ") ),"
aFormula(3) = Chr$(34) & "Please Delete Compliance Data" & Chr$(34)
aFormula(4) = ",IF(AND(ISBLANK(E11),ISBLANK(E13),ISBLANK(E23),ISBLANK(E25),ISBLANK(E26),ISBLANK(E28),ISBLANK(E30),ISBLANK(E31)),"
aFormula(5) = "VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),""Please complete form / SC request""))"

Sheet1.Range("R13").Formula = Join(aFormula, vbNullString)

这篇关于使用Excel VBA插入公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！