Hello,

I am trying to complete an Accounting project and I am having so much trouble with my list box. I am very new to VB and this is my first project. I have an inventory list that has names of Vendors and I need to create a code that will pull the vendors names from the inventory sheet and put them in my summary sheet in the list box. I believe I need to use an array, But I am not sure how to do that?? Here is my code try not to laugh to hard

Private Sub ListBox1_Click()
Dim intRowCount As Integer
Dim strVendor(24) As String
Dim intVendor As Integer
Dim i As Integer

'Complete List Of Vendors

'Move to Inventory
Worksheets("Inventory").Select
'Select cell "H2"
Worksheets("Inventory").Range("H2").
'Select the region that contains "H2" ActiveCell.CurrentRegion.Select
'Transfer the Names of the Vendors to the Summary sheet
Worksheets("summary").Select
For i = 2 To intRowCount
If Range("H" & i).Value <> strVendor(intVendor) Then
intVendor = intVendor + 1
strVendor(intVendor) = Range("H" & i).Value
End If
Next i

For i = 1 To intVendor
Worksheets("Summary").stVendor.AddItem strVendor(i)
Next i
Range("b9") = strVendor(i)

End Sub


THANK YOU

To add items to a listbox you just need to use the Add method.

I already have that in my code I just can't pull the names of the vendors I had put in my array and put them in my list box?

Code:Private Sub ListBox1_Click()
'Variable declaration for number of vendors
Dim intRowCount As Integer
Dim strVendor(30) As String
Dim intVendor As Integer
Dim i As Integer

'Assignment of Summary worksheet values to variables
strVendor(i) = Range("a10")
'Go to inventory worksheet
Worksheets("Inventory").Select
'Count number of rows (this involves several steps)
'Select cell a1
Worksheets("Inventory").Range("a1").Select
'Select the current region that contains the cell a1
ActiveCell.CurrentRegion.Select
'Count the rows that you selected
intRowCount = Selection.Rows.Count
Worksheets("summary").Select
'LOOP from 2 to the number of rows
For i = 2 To intRowCount
'check for vendor names
If Worksheets("Inventory").Range("H" & i).Value <> strVendor(intVendor) Then
intVendor = intVendor + 1
Worksheets("Inventory").Range("H" & i).Value = strVendor(intVendor)
End If
Next i
For i = 1 To intVendor
Worksheets("Summary").ListBox1.AddItem strVendor(i)
Next i
End Sub

?

Actually you don't have it in your code anywhere, you have something completely different.

You need it to read like this:

YOU ARE THE BEST PERSON IN THE WHOLE WORLD AND THE SMARTEST TOO!!

Thank you very much for helping me I am going crazy?!?!

For some reason when I applied the code you gave me it said invalid qualifier?

I'm sorry I just realized this is VBA. Disregard what I said previously.

The problem is that you never assign any values into the array. I think it should be like this:

SERIOUSLY I FREAKIN LOVE YOU!! YOU ARE A VERY NICE PERSON!!!!

I just want to say thank you again!

I have inputed the changes, and the computer is longer yelling at me with errors telling me how wrong I am (Thanks to you), but the names are still not showing up in the list box? Am I the absolute worst person at VBA or what?

here is the code I have so far:
Private Sub ListBox1_Click()
'Variable declaration for number of vendors
Dim i As Integer
Dim intRowCount As Integer
Dim strVendor(30) As String
Dim intVendor As Integer

'Assignment of Summary worksheet values to variables
strVendor(i) = Range("a10")
'Go to inventory worksheet
Worksheets("Inventory").Select
'Count number of rows (this involves several steps)
'Select cell a1
Worksheets("Inventory").Range("a1").Select
'Select the current region that contains the cell a1
ActiveCell.CurrentRegion.Select
'Count the rows that you selected
intRowCount = Selection.Rows.Count
Worksheets("summary").Select
'LOOP (using FOR…NEXT syntax) from 2 to the number of rows
'To begin at start of array
For i = 2 To intRowCount
'check for vendor names
If Worksheets("Inventory").Range("H" & i).Value <> strVendor(intVendor) Then
intVendor = intVendor + 1
'Modified next line to assign value into the array
strVendor(intVendor) = Worksheets("Inventory").Range("H" & i).Value
End If
Next i
For i = 0 To intVendor
Worksheets("Summary").ListBox1.AddItem strVendor(i)
Next i
End Sub



This post has been edited by asugrl08: 26 Nov, 2006 - 08:37 PM

OH MY GOSH TOTALLY DISREGARD THAT I AM SOOOOOOOOO APPRECIATIVE IT WORKED!!! IT REALLY WORKED!!! IF YOU ONLY KNEW HOW MANY CODES I ENTERED AND HOW LONG I HAVE BEEN WORKING WITH THIS ONE DUMB LIST BOX!!! OH THANK YOU SOOOO MUCH

DO you think you could maybe analyze this code for me as well? I am sorry to be bothering you with all these questions! I am trying to say that if the vendor name and the
name on the list box match then the on hand quantity for that vendor in the inventory sheet should be displayed in the on hand quantity cell in the summary sheet

here is my pseudo code:
select inventory wksheet
Count the total number of rows in the inventory sheet
repeat from row#2 to the total number of rows calculated above
If the vendor name for the row(Cell H) is equal to the vendor name in the list box
total_quantity_on_hand=total_quantity_on_hand
+On_Hand_Quantity(Cell D)
End IF
Loop
Select Summary Worksheet
Write the total_quantity_on_hand value to the cell in Summary worksheet

Dim intQuantityOnHand As Integer
Dim intOnHandQuantity As Integer
'Go to inventory worksheet
Worksheets("Inventory").Select
'Count number of rows
'Select cell a1
Worksheets("Inventory").Range("a1").Select
'Select the current region that contains the cell a1
ActiveCell.CurrentRegion.Select
'Count the rows that you selected
intRowCount = Selection.Rows.Count
Worksheets("summary").Select
For i = 0 To 30
If Worksheets("Inventory").Range("H" & i).Value = strVendor _ (intQuantityOnHand) Then
intQuantityOnHand = intQuantityOnHand + intOnHandQuantity
strVendor(intQuantityOnHand) = Worksheets("Inventory").Range("d" & i).Value
End If
Next i
For i = 1 To intVendor
Worksheets("Summary").Select
Next i