PDA

View Full Version : Transferring data from two dimensional VBA array to Excel range -



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.

mdmackillop
04-16-2009, 02:41 PM
Can you post a sample workbook?

musicgold
04-16-2009, 02:46 PM
mdmackillop,

Thanks. Please see the attached file.

MG.

mdmackillop
04-16-2009, 03:09 PM
You arte writing exactly the same formula many times into an array. Just write the formula into the range

.Range("ratios").FormulaR1C1 = "=(RC[-2]/ R[1]C[-2])"

musicgold
04-17-2009, 10:46 AM
mdmackillop,

Thanks. I know that I can get around the problem using different methods.

I was more interested in understanding the reason behind that behaviour, as I also need to write multi-dimensional arrays into Excel ranges in other projects. There arrays are populated with different formulas.

Is it a bug?

Thanks,

MG.

mdmackillop
04-17-2009, 11:23 AM
Transpose fails because there is no need to! Remember also that arrays start at 0

For i = TotalRows - 1 To 0 Step -1

Vma(i, 0) = "=(RC[-2]/R[1]C[-2])"
Vma(i, 1) = "=(RC[-2]/R[1]C[-2])"
Next

.Range("ratios").FormulaR1C1 = Vma

This will fail next because you only specify one dimension.

If Vma(i) <> Empty Then

musicgold
04-17-2009, 01:25 PM
mdmackillop,

Thanks a lot. It works !

MG.