使用VBA在循环中输入带引号的Excel公式 [英] Entering excel formula with quotations in a loop using VBA

问题描述

我正在尝试使用VBA在excel中插入一个公式，该公式类似于excel中的以下内容:

I am trying to use VBA to to insert a formula in excel that would look like the following in excel:

=BDH(C2,"PX LAST",F2,"","Dir=H","days=w","DTS=H","cols=1;rows=1")

这实际上仅表示该公式的第一行，我希望将其复制到特定列中.因此，我必须使用索引为 i 的循环，然后以Cells表示法引用适当的单元格，即 C2 = Cells(i，3)和 F2 = Cells(i，6).在浏览了本站点上的一些讨论以及其他讨论之后，我知道为了在字符串中创建文字引号，您需要使用双引号(" ).

This actually represents just the first row of this formula, which I want to be copied down a certain column. Therefore I have to use a loop with index i and then reference the appropriate cells with Cells notation, i.e. C2=Cells(i,3) and F2=Cells(i,6). After looking through some discussions on this site and others, I know that in order to create a literal quote within a string, you use a double quote ("").

formulaString = "=BDH(Cells" & CStr(i) & ",3),""PX LAST"",Cells(" & CStr(i) & ",6),"""",""Dir=H"",""days=w"",""DTS=H"",""cols=1;rows=1"")"

我想在相应行的第8列中插入这些公式，所以我用

I want to insert these formulas in the 8th column of the corresponding row, so I do this with

Cells(i,8).Formula = formulaString

但是，当我这样做时，出现错误应用程序定义或对象定义的错误".我还看到可以使用代码类似地完成

However, when I do this I get the error "application defined or object-defined error". I also saw that this can similarly be done with the code

Cells(i,8).Value = Evaluate(formulaString)

这不会引发错误，但是当它运行要让公式读取 #Value 的单元格时.

This does not throw an error, but when it runs the cells in which I want the formula read #Value.

有人可以帮助我确定我在做什么错吗?

Could someone please help me identify what I am doing wrong?

推荐答案

您不应将 Cells 放在公式字符串中，而应使用 Address 属性:

You should not be putting the Cells inside the formula string, and you should be using the Address property:

formulaString = "=BDH(" & Cells(i,3).Address(False, False) & ",""PX LAST""," & Cells(i,6).Address(False, False) & ","""",""Dir=H"",""days=w"",""DTS=H"",""cols=1;rows=1"")"

不过，您真正想要的是切换到R1C1表示法:

However what you actually want is switching to the R1C1 notation:

formulaStringR1C1 = "=BDH(RC3,""PX LAST"",RC6,"""",""Dir=H"",""days=w"",""DTS=H"",""cols=1;rows=1"")"

然后使用

Cells(i,8).FormulaR1C1 = formulaStringR1C1

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