Excel VBA 将多个单元格设置为相同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30638716/
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
Excel VBA set multiple cells to the same value
提问by Prot
I want to set every eighth cell on one worksheet to the value of a cell in another worksheet. I wrote this here:
我想将一个工作表上的每八个单元格设置为另一个工作表中一个单元格的值。我在这里写了这个：
Sub xx()
For i = 5 To 45 Step 8
Set ThisWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ThisWorkbook.Sheets("Sheet7").Cells(13, 31).Value
Next i
End Sub
If I try to run it, it gives me a subscript out of range error. Sheet5has also been named Binomial Sheet, do I have to reference this differently? Are there other ways to accomplish this?
如果我尝试运行它，它会给我一个下标超出范围的错误。 Sheet5也被命名为Binomial Sheet，我是否必须以不同的方式引用它？有没有其他方法可以实现这一点？
回答by ZygD
This option of setting the same value for several cells was not yet mentioned:
尚未提及为多个单元格设置相同值的选项：
ActiveSheet.Range("A1,B2,C3:D4").Value = "SomeValue"
回答by Trum
If you are just trying to set the value of one cell to another in this way, you don't need to use the Set property - which is for objects not values.
如果您只是想以这种方式将一个单元格的值设置为另一个单元格，则不需要使用 Set 属性 - 该属性用于对象而非值。
Also ThisWorkbook refers to where the macro is held, but is not suited to calling in this way.
ThisWorkbook 也指的是保存宏的位置，但不适合以这种方式调用。
As such, I'd give: ActiveWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ActiveWorkbook.Sheets("Sheet7").Cells(13, 31).Value
因此，我会给： ActiveWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ActiveWorkbook.Sheets("Sheet7").Cells(13, 31).Value
a go, as this works fine for me.
去吧，因为这对我来说很好用。
回答by Dibstar
The issue seemed to be that you had an end ifwithout a corresponding Ifto start, and to assign a value for the range in sheet 5 you don't need to state setyou can just assign the values directly like so:
问题似乎是您有一个end if没有对应If的开始，并且要为工作表 5 中的范围分配一个值，您无需声明set您可以直接分配值，如下所示：
Sub xx()
For i = 5 To 45 Step 8
ActiveWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ActiveWorkbook.Sheets("Sheet7").Cells(13, 31).Value
Next i
End Sub
回答by Dibstar
Worksheets have a .Name propertyand a CodeName property. Generally speaking, when the worksheets are first created, the two are visually the same but referenced differently. The worksheet's .Name can be changed; the .CodeName cannot be changed.
工作表具有.Name 属性和CodeName 属性。一般来说，第一次创建工作表时，两者在视觉上是相同的，但引用方式不同。工作表的 .Name 可以更改；.CodeName 不能更改。
If Sheet5was renamed Binomial Sheetbut Sheet7was not renamed, this is the difference in referencing them.
如果Sheet5被重命名为Binomial Sheet但Sheet7没有被重命名，这就是引用它们的区别。
dim i as long
with activeworkbook
for i = 5 To 45 step 8
'using the worksheet .Name with the names as quoted strings
.Sheets("Binomial Sheet").Cells(i, 3) = .Sheets("Sheet7").Cells(13, 31).Value
'using the worksheet .CodeName directly
Sheet5.Cells(i, 3) = Sheet7.Cells(13, 31).Value
next i
end with
The .CodeName for Sheet5 did not change when its .Name was changed to Binomial Sheet. The.CodeNames for each worksheet (and its .Name in brackets) are listed within the VBE's Project Explorer(Ctrl+R).
Sheet5 的 .CodeName 在其 .Name 更改为Binomial Sheet时没有更改。VBE 的项目资源管理器( Ctrl+ R)中列出了每个工作表的 .CodeNames（及其括号中的 .Name ）。
