Hey All,

I have a set of 500 or so numbers that i want to use for debugging.
These numbers are in matrix form (30 rows) x (18 cols) and i need to
hard code these numbers into cells for debugging. I would like to
insert these values into a range of the same size ( Range("B2:S31")) is
there a easy way of doing this?

sorta like Range("B2:S31") = [1 , 2, 3, 4......]

i've tried the same thing with populating a multidimensional array and
the only way i can do it is to write one line of code per element of
the array.Can anybody help me with this?

Hi,

This demonstrates how to read in a range into a variant array in 1 go.
Loop through the elements in the array and final output the value again.

Sub Test()

Dim vntArray As Variant
Dim lngRow As Long
Dim lngCol As Long

vntArray = Range("B2:S31")

For lngRow = LBound(vntArray, 1) To UBound(vntArray, 1)
For lngCol = LBound(vntArray, 2) To UBound(vntArray, 2)
Debug.Print vntArray(lngRow, lngCol)
vntArray(lngRow, lngCol) = _
vntArray(lngRow, lngCol) + (lngCol * lngRow)
Next
Next

' uncomment to output changed values
' Range("B2:S31") = vntArray

End Sub

Cheers
Andy

wrote:
Hey All,

I have a set of 500 or so numbers that i want to use for debugging.
These numbers are in matrix form (30 rows) x (18 cols) and i need to
hard code these numbers into cells for debugging. I would like to
insert these values into a range of the same size ( Range("B2:S31")) is
there a easy way of doing this?

sorta like Range("B2:S31") = [1 , 2, 3, 4......]

i've tried the same thing with populating a multidimensional array and
the only way i can do it is to write one line of code per element of
the array.Can anybody help me with this?

Thanks Andy,
but this isn't my problem, my problem comes from taking the raw data
(like a CSV file) and entering it into an array/range

Andy Pope wrote:
Hi,

This demonstrates how to read in a range into a variant array in 1 go.
Loop through the elements in the array and final output the value again.

Sub Test()

Dim vntArray As Variant
Dim lngRow As Long
Dim lngCol As Long

vntArray = Range("B2:S31")

For lngRow = LBound(vntArray, 1) To UBound(vntArray, 1)
For lngCol = LBound(vntArray, 2) To UBound(vntArray, 2)
Debug.Print vntArray(lngRow, lngCol)
vntArray(lngRow, lngCol) = _
vntArray(lngRow, lngCol) + (lngCol * lngRow)
Next
Next

' uncomment to output changed values
' Range("B2:S31") = vntArray

End Sub

Cheers
Andy

wrote:
Hey All,

I have a set of 500 or so numbers that i want to use for debugging.
These numbers are in matrix form (30 rows) x (18 cols) and i need to
hard code these numbers into cells for debugging. I would like to
insert these values into a range of the same size ( Range("B2:S31")) is
there a easy way of doing this?

sorta like Range("B2:S31") = [1 , 2, 3, 4......]

i've tried the same thing with populating a multidimensional array and
the only way i can do it is to write one line of code per element of
the array.Can anybody help me with this?

I'm a little confused, which is not difficult ;)
If you have a csv file can you not open it in excel, which will in
effect put the values in to a matrix. Then read the range in to a array?

Cheers
Andy

wrote:
Thanks Andy,
but this isn't my problem, my problem comes from taking the raw data
(like a CSV file) and entering it into an array/range

Andy Pope wrote:

Hi,

This demonstrates how to read in a range into a variant array in 1 go.
Loop through the elements in the array and final output the value again.

Sub Test()

Dim vntArray As Variant
Dim lngRow As Long
Dim lngCol As Long

vntArray = Range("B2:S31")

For lngRow = LBound(vntArray, 1) To UBound(vntArray, 1)
For lngCol = LBound(vntArray, 2) To UBound(vntArray, 2)
Debug.Print vntArray(lngRow, lngCol)
vntArray(lngRow, lngCol) = _
vntArray(lngRow, lngCol) + (lngCol * lngRow)
Next
Next

' uncomment to output changed values
' Range("B2:S31") = vntArray

End Sub

Cheers
Andy

wrote:

Hey All,

I have a set of 500 or so numbers that i want to use for debugging.
These numbers are in matrix form (30 rows) x (18 cols) and i need to
hard code these numbers into cells for debugging. I would like to
insert these values into a range of the same size ( Range("B2:S31")) is
there a easy way of doing this?

sorta like Range("B2:S31") = [1 , 2, 3, 4......]

i've tried the same thing with populating a multidimensional array and
the only way i can do it is to write one line of code per element of
the array.Can anybody help me with this?