当前位置：theitroad>vba VBA将公式添加到单元格

vba VBA将公式添加到单元格

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/42987975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 10:47:28 　来源:igfitidea点击:

VBA To Add Formula To Cell

excelvbaexcel-vbaexcel-2013

提问by BellHopByDayAmetuerCoderByNigh

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 .Formulait throws a

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

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

回答by OpiesDad

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

问题很可能是您正在使用公式转义引号。

What you need is:

你需要的是：

.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"

etc.

等等。

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

如果不这样做，有时在运行代码时可能会发生错误。

回答by JamesFaix

  1. As suggested by OpiesDad, to minimize ambiguity, avoid ActiveCelland the like.
  2. Using Selectwill also slow down performance a lot compared to assigning to cells directly.
  3. I'm pretty sure you need to escape quotes in Excel formulas inside of VBA by doubling the quotes, so a normal empty string becomes """". You also have Issuein single quotes in a formula, which I'm pretty sure will error in Excel; that should be in escaped double quotes as well.
  4. I'm having a hard time figuring out what Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)actually does, but it sounds like you want to select E2to the last used row of the sheet. Avoid Rows.Countor just generally referring to the rows of a sheet, as that will go to row 10^31. Use Worksheet.UsedRangeto get the range from the first row and column with content to the last row and column with content. This also includes empty strings and can be a bit tricky sometimes, but is usually better than dealing with thousands of extra rows.
  1. 正如 OpiesDad 所建议的，为了尽量减少歧义，避免ActiveCell等。
  2. Select与直接分配给单元格相比，使用也会大大降低性能。
  3. 我很确定您需要通过将引号加倍来转义 VBA 内部 Excel 公式中的引号，因此普通的空字符串变为"""". 您Issue在公式中也有单引号，我很确定在 Excel 中会出错；这也应该在转义的双引号中。
  4. 我很难弄清楚Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)实际做了什么，但听起来您想选择工作表E2的最后使用的行。避免Rows.Count或只是一般地引用工作表的行，因为这将转到第 10^31 行。使用Worksheet.UsedRange从第一行和列的内容到最后的行和列的内容得到的范围内。这也包括空字符串，有时可能有点棘手，但通常比处理数千个额外行要好。

Also,

还，

  1. You don't need to use Withif your only enclosing one statement, although it won't cause any problems.

  2. I would not mix use of Range.Formulaand Range.FormulaR1C1unless you have a reason to.

    Function Gre() 

    Dim ws as Worksheet
    Set ws = ActiveSheet

    Dim used as Range
    Set used = ws.UsedRange

    Dim lastRow as Integer
    lastRow = used.Row + used.Rows.Count - 1

    ws.Range("E2").Formula = "Under"
    ws.Range("F2").Formula = "Over"
    ws.Range("G2").Formula = "Result"

    ws.Range("E2:E" & lastRow).Formula = "IF(C2<B2, C2-B2, """")"
    ws.Range("F2:F" & lastRow).Formula = "IF(C2<B2, C2-B2, 0)"
    ws.Range("G2:G" & lastRow).Formula = "IF(F2>0, ""Issue"", """")"

End Function

  1. 您不需要使用Withif 您只包含一个语句，尽管它不会引起任何问题。

  2. 除非你有理由Range.Formula，Range.FormulaR1C1否则我不会混合使用和。

    Function Gre() 

    Dim ws as Worksheet
    Set ws = ActiveSheet

    Dim used as Range
    Set used = ws.UsedRange

    Dim lastRow as Integer
    lastRow = used.Row + used.Rows.Count - 1

    ws.Range("E2").Formula = "Under"
    ws.Range("F2").Formula = "Over"
    ws.Range("G2").Formula = "Result"

    ws.Range("E2:E" & lastRow).Formula = "IF(C2<B2, C2-B2, """")"
    ws.Range("F2:F" & lastRow).Formula = "IF(C2<B2, C2-B2, 0)"
    ws.Range("G2:G" & lastRow).Formula = "IF(F2>0, ""Issue"", """")"

End Function

回答by Geoffrey Fuller

  1. The first issue is the selecting of cells. This requires the macro to select the cell, then determine the cell address. If you need to actually select a cell, use Application.ScreenUpdating = False. Then the macro doesn't have to show the cursor selection of a cell. Dropping the select and incorporating the range into the formula assignment code line like below will gain some speed/efficiency.

    Range("E2").FormulaR1C1 = "Under"

  2. Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)is the code version of selecting the last cell in a blank column (row 1048576), then using the keystroke of ctrl and the up key to determine the lowest/last used cell. This gets you a row count of 1 every time since the column is blank. Since you're looking for the last row. It may be faster to count down from the top. My favorite method for this is a loop. Increment a variable within a loop, while looking for the last row. Then, the variable can be used instead of your bottom up strategy.

    t = 0
Do Until Range("C2").Offset(t, 0).Value = ""
t = t + 1
Loop

With Range("E2:E" & t)
.Formula = "=IF(C2<B2,B2-C2,"""")"
End With`

  3. Just like TSQL, quote characters need their own quote characters.

    .Formula = "=IF(C2<B2,B2-C2,"""")"
  4. The Range Fillup VBA function can be utilized in this case to fill all cells from the bottom with a common formula, accounting for Excel Formula Reference Relativity. The code below starts with the range that we got from the loop counter. Next, we set a variable equal to the total rows in Excel minus the row corresponding to the counter row. Then, we resize the original region by the necessary rows and use the FillDown function to copy the first formula down.

  5. Here's the resulting code. This will fill the range starting from the last row in Excel. 

     Sub Gre()
 Range("E2").FormulaR1C1 = "Under"
 Range("F2").FormulaR1C1 = "Over"
 Range("G2").FormulaR1C1 = "Result"

 Do While Range("e2").Offset(t, 0).Value <> ""
 t = t + 1
 Loop
 Range("E2").Offset(t, 0).Formula = "=IF(C2<B2,B2-C2,"""")"
 r1 = Range("e2").EntireColumn.Rows.Count
 r2 = Range("E2").Offset(t, 0).Row
 Range("E2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("F2").Offset(t, 0).Formula = "=IF(C2>B2,C2-B2,0)"
 Range("F2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("G2").Offset(t, 0).Formula = "=IF(F2>0,""Issue"","""")"
 Range("G2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 End Sub

  1. 第一个问题是细胞的选择。这需要宏选择单元格，然后确定单元格地址。如果您需要实际选择一个单元格，请使用Application.ScreenUpdating = False. 然后宏不必显示单元格的光标选择。删除选择并将范围合并到公式分配代码行中，如下所示将获得一些速度/效率。

    Range("E2").FormulaR1C1 = "Under"

  2. Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)是在空白列（第 1048576 行）中选择最后一个单元格，然后使用 ctrl 的击键和向上键来确定最低/最后使用的单元格的代码版本。由于该列为空，因此每次都会使您的行数为 1。由于您正在寻找最后一行。从顶部倒数可能会更快。我最喜欢的方法是循环。在循环中递增变量，同时查找最后一行。然后，可以使用该变量代替自下而上的策略。

    t = 0
Do Until Range("C2").Offset(t, 0).Value = ""
t = t + 1
Loop

With Range("E2:E" & t)
.Formula = "=IF(C2<B2,B2-C2,"""")"
End With`

  3. 就像 TSQL 一样，引号字符需要它们自己的引号字符。

    .Formula = "=IF(C2<B2,B2-C2,"""")"
  4. 在这种情况下，可以使用范围填充 VBA 函数从底部填充所有单元格，使用通用公式，考虑 Excel 公式参考相对性。下面的代码从我们从循环计数器获得的范围开始。接下来，我们设置一个变量，等于 Excel 中的总行数减去计数器行对应的行。然后，我们按必要的行调整原始区域的大小，并使用 FillDown 函数向下复制第一个公式。

  5. 这是生成的代码。这将填充从 Excel 中的最后一行开始的范围。 

     Sub Gre()
 Range("E2").FormulaR1C1 = "Under"
 Range("F2").FormulaR1C1 = "Over"
 Range("G2").FormulaR1C1 = "Result"

 Do While Range("e2").Offset(t, 0).Value <> ""
 t = t + 1
 Loop
 Range("E2").Offset(t, 0).Formula = "=IF(C2<B2,B2-C2,"""")"
 r1 = Range("e2").EntireColumn.Rows.Count
 r2 = Range("E2").Offset(t, 0).Row
 Range("E2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("F2").Offset(t, 0).Formula = "=IF(C2>B2,C2-B2,0)"
 Range("F2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 Range("G2").Offset(t, 0).Formula = "=IF(F2>0,""Issue"","""")"
 Range("G2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
 End Sub

回答by Steve C

As well as using double quotes you may need to use 0 in the first two formula otherwise they may evaluate to empty strings. This may give unexpected results for the last formula i.e. incorrectly return "Issue".

除了使用双引号外，您可能还需要在前两个公式中使用 0，否则它们可能会计算为空字符串。这可能会给最后一个公式带来意想不到的结果，即错误地返回“问题”。

If you do not have blank columns between your data and the 3 new columns you can use CurrentRegion to determine the number of rows:

如果您的数据和 3 个新列之间没有空白列，您可以使用 CurrentRegion 来确定行数：

Range("E2:E" & Cells.CurrentRegion.Rows.Count).Formula = "=if(C2'<'B2,B2-C2,0)"

Range("F2:F" & Cells.CurrentRegion.Rows.Count).Formula = "=if(C2>B2,C2-B2,0)"

Range("G2:G" & Cells.CurrentRegion.Rows.Count).Formula = if(F2>0,""Issue"","""")"

相关推荐

vba 如何对集合进行排序？

vba 如何对集合进行排序？

vba Excel 求解器密码：它是什么以及在哪里可以获得/购买？

vba Excel 求解器密码：它是什么以及在哪里可以获得/购买？

如何删除范围 Excel VBA 的列

如何删除范围 Excel VBA 的列

Excel VBA 更改命令按钮的颜色

Excel VBA 更改命令按钮的颜色

相关推荐
最近更新
标签