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").ClearCon tents
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
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").ClearCon
Close
'Open "h:\freshcook\paul\desp20.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER