lundi 30 mars 2015

Declare Cells(row,col) using variables from ARRAY

Is it possible to to declare Cell position with using variables from an array?


Here is my old code which works perfectly but what if I need many more Cells? (I make it shorter). This code by pressing button sets Value of selected cells to zero.



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


And here is my attempt to do it by using 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


But this code doesn't work. So I tried this:



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


But again it doesn't work. Do you have any idea how to put values to array and this use these values as variables to define the position of Cells?


Aucun commentaire:

Enregistrer un commentaire