Fill an Array or Collection without knowing the UBound
- From: RyanH <RyanH@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 4 Nov 2008 13:00:00 -0800
I have a workbook that has a userform in it that the sales team uses to quote
prices for the signs we manufacture. The user calls the userform and then
selects all the specifications of the sign by using checkboxes, option boxes,
comboboxes, etc. I am trying to build a "Bill of Material" for the sign that
the sales person quotes. This "Bill of Material" will contain a parts list
of all the parts used to make that sign, along with the part number, part
price, and part description. I have a master list of all the parts in our
inventory on a worksheet named Sheets("Parts List"). It is laid out like so.
A B C
D
Part Number Part Description Units Part Price
EXT0001111 Aluminum Extrusion ft. $5.50
If a particular part is used in the quoted sign I want to capture the values
in Range A:D on that parts row along with the quantity allocated to that
sign. For example, if PartA is used and is located on Row 100 I want this
stored in an Array or Collection: Range("A100:D100"), 5. 5 being the
quantity of PartA that is used for the sign.
This is my question. If I use an Array how do I fill it with multiple
Ranges and Quantities without knowing how many parts will be used? Or should
I use a collection? Will the collection hold values or just objects?
Once the Array is filled I then will use a loop to fill those values in my
"Bill of Material" worksheet.
For i = LBound(myArray) to UBound(myArray)
' A-part number, B-part description, C-part unit, D-part cost
Sheets("Bill of Material").Range("A10:D10").Value = myArray(i)
' quantity of part used
Sheets("Bill of Material").Range("E10").Value = myArray(i+1)
Next i
--
Cheers,
Ryan
.
- Follow-Ups:
- RE: Fill an Array or Collection without knowing the UBound
- From: Jim Thomlinson
- RE: Fill an Array or Collection without knowing the UBound
- Prev by Date: Re: Horizontal Scrollbar on listbox
- Next by Date: IF Then Statements
- Previous by thread: Type date in first cell of row, 13 columns over - auto extracts month for each row
- Next by thread: RE: Fill an Array or Collection without knowing the UBound
- Index(es):
Relevant Pages
|
