使用ARRAY中的变量声明单元格(行、列)

Question

是否可以使用数组中的变量声明Cell位置？

这是我的旧代码，它工作得很好，但如果我需要更多的单元格呢？（我把它缩短了）。此代码通过按下按钮将所选单元格的值设置为零。

Sub ResetButt01_Click()
Cells(12, 5).Value = 0
Cells(12, 13).Value = 0
Cells(12, 21).Value = 0
Cells(19, 4).Value = 0
Cells(19, 6).Value = 0
Cells(19, 12).Value = 0
End Sub

下面是我尝试使用Array:

Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
Cells(myRow, myCol).Value = 0
End Sub

但是这个代码不起作用。所以我尝试了这个：

Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
Dim r As Integer
Dim c As Integer
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
r = myRow
c = myCol
Cells(r, c).Value = 0
End Sub

但它又不起作用。你知道如何将值放入数组中，并将这些值用作变量来定义单元格的位置吗？

Answer 1

您可以创建一个带有单元格地址的逗号分隔字符串：

Const myCells As String = "A1, B2, C3, D4, E5, F6"
Range(myCells).Value = 0

您也可以创建命名范围myCells2，并简单地调用：

Range("myCells2").Value = 0 

Answer 2

您使用的数组方法实际上比写入范围慢得多，因为您正在写入每个单独的单元格

您最好定义一个范围，并在sigle快照中写入，代码也更容易阅读。在我的测试中，这比你选择的答案快4倍。

Sub ResetButt02_Click()
Dim rng1 As Range
Set rng1 = Range("E12,M12,U12,D19,E19,l19")
rng1.Value2 = vbNullString
End Sub

更新

设置单个范围时，限制为255个字符。要么像以前那样使用两个范围，要么使用Union将两个范围网格化在一起：

Sub ResetButt01_Kliknút()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("C33,C40,C67,C74,C88,C95,D19,D53,E12,E26,E33,E40,E47,E60,E67,E74,E81,E88,E95,E102,F19,F53,G33,G40")
Set rng2 = Range("G67,G74,G88,G95,K53,K60,L19,L26,L88,M12,M33,M40,M47,M53,M60,M67,M74,M81,M95,M102,N19,N26,N88,O53,O60,T26,T33,T67,T74,T81,T88,U12,U19,U40,U47,U53,U60,U95,U102,V26,V33,V67,V74,V81,V88")
Set rng2 = Union(rng1, rng2)
rng2.Value2 = 0
End Sub

Answer 3

使用阵列

Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
Dim i As Long, R As Long, C As Long
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
For i = 0 To 5
    R = myRow(i)
    C = myCol(i)
    Cells(R, C).Value = 0
Next i
End Sub

Answer 4

尝试使用循环：

Option Explicit
Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
Dim i As Integer
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
For i = LBound(myRow) To UBound(myRow)
    Cells(myRow(i), myCol(i)).Value = 0
Next i
End Sub

M。