在 Excel 和 VBA 中，如何将单元格的值设置为公式结果而不是公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12737642/
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
In Excel and VBA, how to set value of cell to the formulas result rather than the formula
提问by Kurt
I'm getting values from one sheet and placing them in another using a macro in Excel. I currently have this which works fine:
我从一张工作表中获取值，然后使用 Excel 中的宏将它们放在另一张工作表中。我目前有这个工作正常：
sheet.range("B2:B35").Value = "=IF(SUMPRODUCT(--(Raw!$B:$B2=$A2),--(Raw!$D:$D2=All!$B),Raw!$H:$H2)<>0,SUMPRODUCT(--(Raw!$B:$B2=$A2),--(Raw!$D:$D2=All!$B),Raw!$H:$H2),""-"")"
It, obviously, puts that entire formula as the value of the cell. What I'd like is it just to put the result of the formula into the cell. I've tried adding Evaluate() around the "IF..." part, but then the IF doesn't evaluate correctly (I just end up with "-" in each cell). Is this possible to do or do I have to have separate code to loop through and change the value to the value of the cell?
显然，它将整个公式作为单元格的值。我想要的是将公式的结果放入单元格中。我已经尝试在“IF...”部分周围添加 Evaluate() ，但随后 IF 无法正确评估（我只是在每个单元格中以“-”结尾）。这是可能的还是我必须有单独的代码来循环并将值更改为单元格的值？
回答by mkingston
Use:
用：
sheet.range("B2:B35").Formula = "Your formula here"
If that doesn't work you may have to change the formatting (do this first):
如果这不起作用，您可能需要更改格式（先执行此操作）：
sheet.range("B2:B35").NumberFormat = "General"
Edit:
编辑：
A solution turned out to be addition of the following line after the OP's code:
一个解决方案是在 OP 的代码之后添加以下行：
sheet.range("B2:B35").value = sheet.range("B2:B35").value
