musicgold
04-16-2009, 02:39 PM
Hi,
I am trying to transfer data from VBA arrays into Excel ranges. Please see the following code.
It is working fine for one dimensional arrays and ranges; however, partially not working for the array Vma, which has two dimensions (or columns). By partially I mean I am able to transfer some contents of Vma into a two-column range (first two rows out of 500 rows), for all other rows Excel inserts #N/A into the cells. I tried different permutations and combinations, to no avail. What could be the problem?
Dim Buycol ()
Dim Sellcol ()
Dim Vma ()
...
TotalRows = .Range("Master").Count ‘ roughly 500 rows
ReDim Buycol(TotalRows)
ReDim Sellcol(TotalRows)
ReDim Vma(TotalRows, 1)
For i = TotalRows To 0 Step -1
Vma(i, 0) = "=Average(RC[-2]:R[3]C[-2])"
Vma(i, 1) = "=Average(RC[-2]:R[3]C[-2])"
Buycol(i) = "=Average(RC[-2]:R[3]C[-2])"
Sellcol(i) = "=Average(RC[-2]:R[3]C[-2])"
Next
.Range("sellcolumn") = Application.WorksheetFunction.Transpose(Sellcol)
.Range("ratios") = Application.WorksheetFunction.Transpose(Vma) ‘ NOT WORKING
.Range("buycolumn") = Application.WorksheetFunction.Transpose(Buycol)
Thanks,
MG.
I am trying to transfer data from VBA arrays into Excel ranges. Please see the following code.
It is working fine for one dimensional arrays and ranges; however, partially not working for the array Vma, which has two dimensions (or columns). By partially I mean I am able to transfer some contents of Vma into a two-column range (first two rows out of 500 rows), for all other rows Excel inserts #N/A into the cells. I tried different permutations and combinations, to no avail. What could be the problem?
Dim Buycol ()
Dim Sellcol ()
Dim Vma ()
...
TotalRows = .Range("Master").Count ‘ roughly 500 rows
ReDim Buycol(TotalRows)
ReDim Sellcol(TotalRows)
ReDim Vma(TotalRows, 1)
For i = TotalRows To 0 Step -1
Vma(i, 0) = "=Average(RC[-2]:R[3]C[-2])"
Vma(i, 1) = "=Average(RC[-2]:R[3]C[-2])"
Buycol(i) = "=Average(RC[-2]:R[3]C[-2])"
Sellcol(i) = "=Average(RC[-2]:R[3]C[-2])"
Next
.Range("sellcolumn") = Application.WorksheetFunction.Transpose(Sellcol)
.Range("ratios") = Application.WorksheetFunction.Transpose(Vma) ‘ NOT WORKING
.Range("buycolumn") = Application.WorksheetFunction.Transpose(Buycol)
Thanks,
MG.