Consulting

Results 1 to 5 of 5

Thread: Array

  1. #1
    VBAX Regular
    Joined
    Dec 2004
    Posts
    93
    Location

    Array

    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.

  2. #2
    VBAX Contributor Aaron Blood's Avatar
    Joined
    Sep 2004
    Location
    Palm Beach, Florida, USA
    Posts
    130
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Dec 2004
    Posts
    93
    Location
    Thanks for your help. It worked.

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hey Kaizer, did you know that you can mark your own threads Solved? Go to Thread Tools --> Mark Solved --> Perform Action.

  5. #5
    VBAX Regular
    Joined
    Dec 2004
    Posts
    93
    Location
    Quote Originally Posted by firefytr
    Hey Kaizer, did you know that you can mark your own threads Solved? Go to Thread Tools --> Mark Solved --> Perform Action.
    Now I know!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •