我正在编写一个程序，用于搜索包含奥林匹克拳击数据的电子表格。用户使用组合框选择国家/地区和体重级别，然后点击 Go！按钮，程序调用 Excel 电子表格中的宏，如下所示：
Sub paramedals(class As String, country As String)
MsgBox " Counting by weight class " & class & " and country " & country
Range("I73").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(C[-7], class,C[-6],country,C[-4], ""Gold"")"
End Sub
我输入了 msgbox 命令以确保它正确读取参数 - 确实如此。问题在于，它不是将公式的参数设置为给定的字符串，而是字面上将公式设置为 COUNTIFS(B:B、类等)。
就像我之前说的，我知道正确的字符串被发送到宏，因为 msgbox，它显示正确。可以不使用 COUNTIFS 参数吗？
I am writing a program which searches a spreadsheet full of Olympic Boxing data. Using a combobox, the user selects a country, and a weight class, and upon hitting the Go! button, the program calls a macro in the Excel Spreadsheet which looks like this:
Sub paramedals(class As String, country As String)
MsgBox " Counting by weight class " & class & " and country " & country
Range("I73").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(C[-7], class,C[-6],country,C[-4], ""Gold"")"
End Sub
I put in the msgbox command to make sure it was reading the parameters correctly - and it is. The problem is that, rather than setting the formula's parameters to the given strings, it is literally setting the formula to COUNTIFS(B:B, class, etc. .).
Like I said before, I know for a fact that the correct strings are being sent to the macro, because of the msgbox, it displays correctly. Can you just not use parameters for COUNTIFS?
发布评论
评论（2）
您需要将公式的字符串部分与变量分开
编辑：
这两种方法都有效，一个是 R1C1，另一个是标准公式
一个好的技巧是使用 debug.print 构建语句或 msgbox 首先，然后你可以看到进入单元格时它的样子。
You need to separate out the string part of the formula from the variables
Edit:
Both of these work, one is R1C1 and the other is a standard formula
A good tip is to build the statement using debug.print or msgbox first and then you can see how it looks when going into a cell.
或者，您可以替换
By
当然，只有当您只能显示值而不是公式时，它才有效。
Alternatively, you could replace
By
Of course, it only works if you can show only the value instead of the formula.