I have a first row with 100 cells and I created an Array of Strings, which represent the new row content. I would like to replace the content of all the first row with the content of my Array in VBA, how can I do that?
Add a comment
|
2 Answers
Say your array is called myArray, it's enough to do this:
For j = LBound(myArray) To UBound(myArray)
Sheets("your sheet").Cells(1,j+1).Value = myArray(j)
Next j
The functions LBound() and UBound() are respectively returning the first and the last index of your array.
Please note that when writing Cells(1,j+1) I'm assuming two important things:
1) Your start index starts with 0, so I want to start the insertion of the values from the column 1 (j+1 = 0+1 = 1).
2) You want to override the first row (because the row index is equal to 1).
You might want to customize this, for example creating independent indexes - when I say "independent", I mean "not depending on the lower and the upper bound of your array, nor being hard-coded like I did for the "row 1".
Comments
You can read and write between a Range and an Array in one line. It is more efficient than using a loop.
Note: The array must be 2 Dimensional to write to a range.
Public Sub ReadToArray()
' Create dynamic array
Dim StudentMarks() As Variant
' Read values into array from 100 cells in row 1
StudentMarks = Sheets("Sheet1").Range("A1:CV1").Value
' Do something with array
' Write the values back to sheet
Sheets("Sheet1").Range("A1:CV1").Value = StudentMarks
End Sub
