Excel中的宏和公式的问题 [英] Issues with macros and formulas in Excel

问题描述

在Excel中从宏中插入公式时，我遇到问题。我设法插入完全相同的值，我在另一个单元格上公式正常工作。它总是说＃¿NAME？除非我按F2和回车。我检查并重新检查计算模式，而不是手动，设置为自动，但这个公式根本不起作用。 SendKeys{f2}SendKeys{enter}的方法似乎有效，但可怕而且并不无缝。

我尝试过使用不同的公式更简单到更复杂），并且每次在我的公式中尝试任何函数都不行。插入工作公式的唯一方法是使用数字（例如= 1 + 1和类似的东西），但是当使用任何函数为ex。 = SUM（A1：A5）只是不工作（除非按F2和Enter）。

我不明白为什么要有完全相同的文字作为公式插入，它不工作。
我该怎么办才能解决这个问题？
Thanx提前

解决方案

请参阅怪物的答案，以解决问题的正确解决方案。

我必须将此答案留在这里，直到它被接受然后我可以删除它。

---

要输入区域设置相关公式，您需要使用 FormulaLocal ：

 单元格（LastRow + 1，3）.FormulaLocal == SI .ERROR（BUSCARV（B& laststr&Datos！A2：E52,3））
  

I'm having problems when inserting a formula from a macro in Excel. I managed to insert exactly the same value that I have on another cell where the formula is working perfectly. It always says #¿NAME? unless I press F2 and Enter. I checked and rechecked the calculation mode and is not Manual, is set to automatic but this formulas doesn't work at all. The method SendKeys "{f2}" SendKeys "{enter}" seems to work but it's horrible and not "seamless".

I've tried with different formulas (from simpler to more complex) and every time a tried any function inside my formula just doesn't work. The only way I insert a working formula is just using numbers (ex. "=1+1" and stuff like that) but when using any function for ex. "=SUM(A1:A5)" just don't work (unless pressing F2 and Enter).

I don't understand why having exactly the same "text" inserted as Formula it just don't work. What can I do to solve this??? Thanx in advance

解决方案

Please refer to the answer by Monster for the correct solution to the problem.

I have to leave this answer here until it is "unaccepted", and then I will be able to delete it.

---

To enter locale-dependent formulas, you need to use FormulaLocal:

Cells(LastRow + 1, 3).FormulaLocal = "=SI.ERROR(BUSCARV(B" & laststr & " , Datos!A2:E52, 3), """")"

这篇关于Excel中的宏和公式的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！