PDA

View Full Version : [SOLVED] Array



Kaizer
01-10-2005, 07:55 AM
I have a table where in column A I have Entities that receive Products and in row A - Entities that send products. In Intersections of EntitiyFrom and EntityTo I have values.

Entity1 Entity2 Entity3
Entity1 0 5 6
Entity2 12 0 2
Entity3 6 1 0

In many cases the value would be 0. So I want to get rid off 0 and paste the values in the following way:

Entity1 Entity2 5
Entity1 Entity3 6
Entity2 Entity1 12
Entity2 Entity3 2
Entity3 Entity1 6
Entity3 Entity2 1


My understanding is that I have to loop through the range and put the values into an Array. Afterwards, I need to transfer the data from Array into Excel. Can you help with the procedure?

I attach the file where I started to make the procedure but got stuck.

Thank you in advance.

Aaron Blood
01-10-2005, 09:33 AM
Option Base 1

Sub Matrix_2_Array()
Dim xArray As Variant
Dim xRange As Range, yRange As Range
Dim r As Double, c As Double
Dim i As Double, j As Double, k As Double
Set xRange = Range("A1:E5") 'matrix location
Set yRange = Range("G1") 'output location
r = xRange.Rows.Count
c = xRange.Columns.Count
ReDim xArray(r, c)
xArray = xRange.Value 'Notice how I populate the array without looping.
For i = 2 To UBound(xArray, 1)
For j = 2 To UBound(xArray, 2)
If Not xArray(i, j) = 0 Then
With yRange
.Offset(k, 0).Value = xArray(i, 1)
.Offset(k, 1).Value = xArray(1, j)
.Offset(k, 2).Value = xArray(i, j)
End With
k = k + 1
End If
Next j
Next I
End Sub

Kaizer
01-10-2005, 12:31 PM
Thanks for your help. It worked.

Zack Barresse
01-10-2005, 12:41 PM
Hey Kaizer, did you know that you can mark your own threads Solved? Go to Thread Tools --> Mark Solved --> Perform Action. :yes

Kaizer
01-10-2005, 12:47 PM
Hey Kaizer, did you know that you can mark your own threads Solved? Go to Thread Tools --> Mark Solved --> Perform Action. :yesNow I know! :)