Link to home
Start Free TrialLog in
Avatar of Bez66
Bez66

asked on

Create List box from an array

Hi all, I've VBA code that brings data from a network drive csv file. I want to at run time be promted to select from a list box an object in the array (trailer) and continue to run based upon my selection, this is the current code;

Sub RunLoad()
'
' RunLoad Macro
' Macro 22/03/2010 by bez66


    Sheets("Load Details").Select
   
    Dim x1, x2, x3, x4, trailer, itemCode, itemUpc, itemDescription, uom As String
    Dim quantity, total As Double
    Dim j, row, column, count, xcount, x, h, a As Integer
    Dim found, xfound As Boolean
   
    Dim trailers(100)
    Dim itemCodes(500, 2)
   
    Range("A6:Z1000").ClearContents
    Close
    'Open "h:\freshcook\paul\desp20.txt" For Input As 1
    Open "z:\desp20.txt" For Input As 1
    Do Until EOF(1)
        Input #1, x1, x2, x3, x4, trailer, itemCode, itemUpc, itemDescription, quantity, uom
        If x1 <> "0" Then
                       
            'create trailer array - sets column for each trailer number
            found = False
            For j = 1 To count
                If trailer = trailers(j) Then
                    found = True
                    Exit For
                End If
            Next
            If found = False Then
                count = count + 1
                trailers(count) = trailer
                                               
            End If
                       
            'create itemCode array - sets row for each item code
            xfound = False
            For x = 1 To xcount
                If itemCode = itemCodes(x, 1) Then
                    xfound = True
                    Exit For
                End If
            Next
            If xfound = False Then
                xcount = xcount + 1
                row = row + 1
                itemCodes(xcount, 1) = itemCode
                itemCodes(xcount, 2) = row
            End If
                                   
            'output data
            Cells(x + 6, 1) = itemCode
            Cells(x + 6, 2) = itemUpc
            Cells(x + 6, 3) = itemDescription
            Cells(x + 6, j + 3) = quantity
            Cells(6, j + 3) = arr
                   
        End If
       
    Loop
   
       
    'row totals
    h = 6
    Cells(6, count + 4) = "TOTALS"
    Do
        h = h + 1
        If Cells(h, 1) = "" Then Exit Do
        For a = 1 To count
            total = total + Cells(h, a + 3)
        Next
        Cells(h, a + 3) = total: total = 0
    Loop
   
   
    'column totals
    row = 6
    column = 3
    total = 0
    Do
        column = column + 1
        If Cells(6, column - 1) = "TOTALS" Then Exit Do
        Do
            row = row + 1
            If Cells(row, 1) = "" Then Exit Do
            total = total + Cells(row, column)
        Loop
        Cells(row, column) = total
        total = 0
        row = 6
    Loop




End Sub
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bez66
Bez66

ASKER

Many thanks for the advice.