在带有变量的 VBA 函数中使用单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14929855/
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
Use a cell value in VBA function with a variable
提问by Nicolas
I'm new to VBAand I can't manage to do what I want although it's very simple.
我是新手，VBA虽然很简单，但我无法做我想做的事。
I need to automatically modify cells of a big (333x333) empty (full of zeros) spreadsheet.
我需要自动修改大（333x333）空（全零）电子表格的单元格。
In a separate spreadsheet I have the row and column of all the cells to modify. (5000 of them)
在单独的电子表格中，我要修改所有单元格的行和列。（其中 5000 个）
A forloop seems to be suited for this purpose.
一for环似乎适用于这一目的。
Here is the code of my macro. The problem appears on the line before the last one.
这是我的宏的代码。问题出现在最后一个之前的行上。
Dim val1 As String, val2 As String, i As Integer
For i = 1 To 333
Sheets("Feuil2").Activate
ActiveSheet.Cells(i, 1).Select
val1 = Cells(i, 1).Value
val2 = Cells(i, 2).Value
Sheets("Classeur2.csv").Select
Cells(val1, val2).Select
ActiveCell.FormulaR1C1 = "1"
Next i
The line that causes a problem is this one : Cells(val1, val2).Select
导致问题的那一行是： Cells(val1, val2).Select
I believe my error is a syntax error. But I can't find out what I should add before, after or around my two variables "val1" and "val2"
我相信我的错误是语法错误。但我不知道我应该在我的两个变量“val1”和“val2”之前、之后或周围添加什么
What do you think ?
你怎么认为 ？
Thanks a lot for your help. Nicolas.
非常感谢你的帮助。尼古拉斯。
Edit
编辑
My problem is now solved :
我的问题现在解决了：
The first answer is exactly what I needed to male my macro work. The second answer is the proper and faster way to do it.
第一个答案正是我在宏观工作中所需要的。第二个答案是正确且更快的方法。
回答by Peter Albert
No need to activate or selection sheets or cells if you're using VBA. You can access it all directly. The code:
如果您使用 VBA，则无需激活或选择工作表或单元格。您可以直接访问所有内容。编码：
Dim rng As Range
For Each rng In Sheets("Feuil2").Range("A1:A333")
Sheets("Classeur2.csv").Cells(rng.Value, rng.Offset(, 1).Value) = "1"
Next rng
is producing the same result as Joe's code.
产生与乔的代码相同的结果。
If you need to switch sheets for some reasons, use Application.ScreenUpdating = Falseat the beginning of your macro (and Application.ScreenUpdating=Trueat the end). This will remove the screenflickering - and speed up the execution.
如果由于某些原因需要切换工作表，请Application.ScreenUpdating = False在宏的开头（和Application.ScreenUpdating=True结尾）使用。这将消除屏幕闪烁 - 并加快执行速度。
回答by Joe
VAL1 and VAL2 need to be dimmed as integer, not as string, to be used as an argument for Cells, which takes integers, not strings, as arguments.
VAL1 和 VAL2 需要变暗为整数，而不是字符串，以用作 Cells 的参数，它采用整数而不是字符串作为参数。
Dim val1 As Integer, val2 As Integer, i As Integer
For i = 1 To 333
Sheets("Feuil2").Activate
ActiveSheet.Cells(i, 1).Select
val1 = Cells(i, 1).Value
val2 = Cells(i, 2).Value
Sheets("Classeur2.csv").Select
Cells(val1, val2).Select
ActiveCell.FormulaR1C1 = "1"
Next i
