从Excel宏向单元格添加公式 [英] Adding Formula to Cell from Excel Macro

问题描述

我在工作表2中有一个宏按钮，希望它将公式放入工作表1的列中. 例如，我可以做一个简单的求和公式，例如:

I have a macro button in worksheet 2 and want it to put a formula into a column of worksheet 1. For example I can do a simple sum formula such as:

Sheets("worksheet1").Range("I:I") = "=SUM(M:M)"

这有效，但是当我尝试使用实际更复杂的公式进行操作时，我希望它不起作用. 为什么会这样?

This works but when I try and do it with the actual more complicated formula I want it will not work. Why is this?

Sheets("worksheet1").Range("I:I") = "=IF(ISNUMBER(SEARCH("*567*",B:B)),"INSTOCK","")"

推荐答案

像您一样写双引号会使VBA认为您在编写"=IF(ISNUMBER(SEARCH("后就结束了字符串.实际上，此代码将出错.您需要将报价加倍.理解所写内容的一种好方法是首先使用Debug.Print:

Writing a double quote like you did makes VBA think you ended your string after just writing "=IF(ISNUMBER(SEARCH(". In fact, this code will error out. You'll need to double-up your quotes. A great way to understand what you are writing would be to use Debug.Print first:

Debug.Print "=IF(ISNUMBER(SEARCH(""*567*"",B:B)),""INSTOCK"","""")"

所以这将起作用:

Sheets("worksheet1").Range("I:I") = "=IF(ISNUMBER(SEARCH(""*567*"",B:B)),""INSTOCK"","""")"

注意::由于您使用的是整个列引用，因此这将对您的计算造成很大的负担！

Note: since you are using whole column references, this is going to be heavy on your calculation!

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