Write matrix in vba to excel

[cogivina]

Hi,

I need help to write the value of a matrix, mtrxA(rows, columns), from vba into excel...Thanks.

Dim mtrxA(1 To Rows, 1 To Cols)

mtrxA(1, 1) = 1
mtrxA(1, 2) = 2
............
mtrxA(Rows, Cols) = ###

Select Case Range("A1")
Case ""
Range("A1:C" & Cols) = mtrxA
Case Else
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell() = mtrxA ------- ???????
End Select

 

[TonyJollans]

You can simply set a range, of the appropriate size, to an array so all you need to do is identify the top left cell, measure from there and go ...

Select Case Range("A1")
Case ""
Range("A1").Resize(Rows, Cols) = mtrxA
Case Else
Range("A1").End(xlDown).Offset(1, 0).Resize(Rows, Cols) = mtrxA
End Select

No need to select anything. Only thing to watch out for is that the End(xlDown) does what you want - which depends on your data.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[cogivina]

Hi Tony,

The resize code really simplifies a lot for my programs. I do have another question. How do you repeat just a certain rows in the matrix?

mtrxA(rows 11...14,) = mtrxA(rows 1...4,)

Thanks.

 

[TonyJollans]

No easy way I'm afraid - you just have to copy the individual elements.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association