VBA将公式添加到单元格 [英] VBA To Add Formula To Cell

问题描述

我正在尝试编写一些VBA，它将在3个单元格中添加标题文本，然后将公式一直填充到最后一行.我已经写了下面的代码，它没有给标题写任何问题，但是当它到达我的第一个 .Formula 时，它会抛出一个

I am attempting to write some VBA which will add header text to 3 cells then fill a formula all the way down to the last row. I have written the below, which writes the headers no problems, but when it get's to my first .Formula it throws a

应用定义错误或对象定义错误

Application Defined or Object Defined error

需要更改什么才能使该宏成功执行?(这些公式是直接从单元格中的公式中提取的，因此我知道它们至少在前端"是有效的公式)

What needs to be altered so that this macro will execute successfully? (The formulas were pulled directly from the formula in the cell, so I know they are valid formulas at least on the "front-end")

Function Gre()
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Under"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Over"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "Result"

    With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2<B2,B2-C2,"")"
    End With
    With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2>B2,C2-B2,0)"
    End With
    With Range("G2:G" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(F2>0,'Issue',"")"
    End With
End Function

推荐答案

问题很可能是您用公式转义了引号.

The problem is likely that you are escaping the quotes with the formula.

您需要的是:

.Formula = "=IF(C2>B2,B2-C2,"""")"

例如，对于第一个

.其他引号也需要加倍.

for the first one, for example. The other quotes need to be doubled as well.

作为旁注，最好使用类似以下的内容指定正在处理的工作表:

As a side-note, it would also be best to specify the sheet you are working on with something like:

 Dim ws as worksheet
 Set ws = Sheets("mySheet")
 ws.Range("E2").FormulaR1C1 = "Under"

等

如果不执行此操作，则有时可能会在运行代码时发生错误.

If you don't do this, you can sometimes have errors happen while running the code.

这篇关于VBA将公式添加到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！