Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
One way would be to create a user defined type. This must be placed in a
standard code module... Type Material rngItem As Range dblQuantity As Double End Type You can use it like this... Sub test() Dim udtMaterials() As Material ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A101:D101") MsgBox udtMaterials(0).rngItem.Address & vbCrLf & udtMaterials(0).dblQuantity MsgBox udtMaterials(1).rngItem.Address & vbCrLf & udtMaterials(1).dblQuantity End Sub -- HTH... Jim Thomlinson "RyanH" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
Sorry... Only the declaration of the UDT needs to be placed in a standard
code module. The rest of the code can reside anywhere as the UDT is implicitly declared as public (since I did not specify). From re-reading my post it seems that part is not too clear. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: One way would be to create a user defined type. This must be placed in a standard code module... Type Material rngItem As Range dblQuantity As Double End Type You can use it like this... Sub test() Dim udtMaterials() As Material ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A101:D101") MsgBox udtMaterials(0).rngItem.Address & vbCrLf & udtMaterials(0).dblQuantity MsgBox udtMaterials(1).rngItem.Address & vbCrLf & udtMaterials(1).dblQuantity End Sub -- HTH... Jim Thomlinson "RyanH" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
Thanks for the reply Jim. I just want to understand exactly what is going on
here. By setting a user defined type are you creating an Object (Material) with properties rngItem and dblQuantity, or something else? Is there a way to configure the code to know what the next index number is, instead of explicitly assigning the index? For example, I would need something like this udtMaterial(UBound(udtMaterial) + 1), instead of udtMaterial(1). Can this be done with an Array? The reason I need to do this is because sometimes certain parts will be used and sometimes they will not, depending on what the sales person quotes. This may present a problem using a loop to list the ranges and quantities on my "Bill of Material" worksheet. If untMaterials(1) has nothing assigned to it I will probably get an error, right? For example, Type Material rngItem As Range dblQuantity As Double End Type Sub test() Dim udtMaterials() As Material If chkPainted = True Then ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") End IF If chkMountedOnFrame = True Then ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A76:D76") End If If cboIllumination.ListIndex = 2 Then ReDim Preserve udtMaterials(2) udtMaterials(2).dblQuantity = 6 Set udtMaterials(2).rngItem = Range("A52:D52") End If End Sub I know I am throwing a lot of questions out there and I appreciate your response! -- Cheers, Ryan "Jim Thomlinson" wrote: Sorry... Only the declaration of the UDT needs to be placed in a standard code module. The rest of the code can reside anywhere as the UDT is implicitly declared as public (since I did not specify). From re-reading my post it seems that part is not too clear. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: One way would be to create a user defined type. This must be placed in a standard code module... Type Material rngItem As Range dblQuantity As Double End Type You can use it like this... Sub test() Dim udtMaterials() As Material ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A101:D101") MsgBox udtMaterials(0).rngItem.Address & vbCrLf & udtMaterials(0).dblQuantity MsgBox udtMaterials(1).rngItem.Address & vbCrLf & udtMaterials(1).dblQuantity End Sub -- HTH... Jim Thomlinson "RyanH" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
not sure if this will help, but here's a snippet i've used in that past. maybe
someone will find fault with it, but it works for me. Dim itmArr() As Variant ReDim itmArr(0) With ws Do While j <= endrow And .Range("K" & j).Value <= 40 ReDim Preserve itmArr(0 To z) itmArr(z) = .Range("A" & j & ":B" & j).value z = z + 1 j = j + 1 Loop End With For w = LBound(itmArr) To UBound(itmArr) 'do something next -- Gary "RyanH" wrote in message ... Thanks for the reply Jim. I just want to understand exactly what is going on here. By setting a user defined type are you creating an Object (Material) with properties rngItem and dblQuantity, or something else? Is there a way to configure the code to know what the next index number is, instead of explicitly assigning the index? For example, I would need something like this udtMaterial(UBound(udtMaterial) + 1), instead of udtMaterial(1). Can this be done with an Array? The reason I need to do this is because sometimes certain parts will be used and sometimes they will not, depending on what the sales person quotes. This may present a problem using a loop to list the ranges and quantities on my "Bill of Material" worksheet. If untMaterials(1) has nothing assigned to it I will probably get an error, right? For example, Type Material rngItem As Range dblQuantity As Double End Type Sub test() Dim udtMaterials() As Material If chkPainted = True Then ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") End IF If chkMountedOnFrame = True Then ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A76:D76") End If If cboIllumination.ListIndex = 2 Then ReDim Preserve udtMaterials(2) udtMaterials(2).dblQuantity = 6 Set udtMaterials(2).rngItem = Range("A52:D52") End If End Sub I know I am throwing a lot of questions out there and I appreciate your response! -- Cheers, Ryan "Jim Thomlinson" wrote: Sorry... Only the declaration of the UDT needs to be placed in a standard code module. The rest of the code can reside anywhere as the UDT is implicitly declared as public (since I did not specify). From re-reading my post it seems that part is not too clear. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: One way would be to create a user defined type. This must be placed in a standard code module... Type Material rngItem As Range dblQuantity As Double End Type You can use it like this... Sub test() Dim udtMaterials() As Material ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A101:D101") MsgBox udtMaterials(0).rngItem.Address & vbCrLf & udtMaterials(0).dblQuantity MsgBox udtMaterials(1).rngItem.Address & vbCrLf & udtMaterials(1).dblQuantity End Sub -- HTH... Jim Thomlinson "RyanH" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill an Array or Collection without knowing the UBound
This is a better example of my code. I'm not to sure if the udt is the best
option. What is your opinoin? Should I use an Array instead? Plus I am getting an error as indicated below: Sub or Function not Defined. this is in a Standard Module: Type Material rngDes As Range dblQty As Double curCost As Currency End Type this is in a Userform: Sub cmbCalculate_Click() 'EXTRUSION MATERIAL COST Dim udtMaterials() As Material If CheckBox1 = True Then ' TopDoor ReDim udtMaterials(0) Error Material(0).dblQty = tbxCabSizeWft + tbxCabSizeWins / 12 Set Material(0).rngDes = PartInfo("EXT00011742") Material(0).curCost = PartCost("EXT00011742") End If If CheckBox2 = True Then ' SideDoor ReDim Preserve udtMaterials(1) Material(1).dblQty = 2 * (tbxCabSizeHft + tbxCabSizeHins / 12) + tbxCabSizeWft + tbxCabSizeWins / 12 Set Material(1).rngDes = PartInfo("EXT00011741") Material(1).curCost = PartCost("EXT00011741") End If If CheckBox3 = True Then ' TopFram ReDim Preserve udtMaterials(2) Material(2).dblQty = tbxCabSizeWft + tbxCabSizeWins / 12 Set Material(2).rngDes = PartInfo("EXT00011743") Material(2).curCost = PartCost("EXT00011743") End If Dim PartsSum As Currency Dim i As Long For i = 0 To Material.curCost.Count PartsSum = PartsSum + Material(i).curCost Next i MsgBox PartsSum End Sub -- Cheers, Ryan "RyanH" wrote: Thanks for the reply Jim. I just want to understand exactly what is going on here. By setting a user defined type are you creating an Object (Material) with properties rngItem and dblQuantity, or something else? Is there a way to configure the code to know what the next index number is, instead of explicitly assigning the index? For example, I would need something like this udtMaterial(UBound(udtMaterial) + 1), instead of udtMaterial(1). Can this be done with an Array? The reason I need to do this is because sometimes certain parts will be used and sometimes they will not, depending on what the sales person quotes. This may present a problem using a loop to list the ranges and quantities on my "Bill of Material" worksheet. If untMaterials(1) has nothing assigned to it I will probably get an error, right? For example, Type Material rngItem As Range dblQuantity As Double End Type Sub test() Dim udtMaterials() As Material If chkPainted = True Then ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") End IF If chkMountedOnFrame = True Then ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A76:D76") End If If cboIllumination.ListIndex = 2 Then ReDim Preserve udtMaterials(2) udtMaterials(2).dblQuantity = 6 Set udtMaterials(2).rngItem = Range("A52:D52") End If End Sub I know I am throwing a lot of questions out there and I appreciate your response! -- Cheers, Ryan "Jim Thomlinson" wrote: Sorry... Only the declaration of the UDT needs to be placed in a standard code module. The rest of the code can reside anywhere as the UDT is implicitly declared as public (since I did not specify). From re-reading my post it seems that part is not too clear. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: One way would be to create a user defined type. This must be placed in a standard code module... Type Material rngItem As Range dblQuantity As Double End Type You can use it like this... Sub test() Dim udtMaterials() As Material ReDim udtMaterials(0) udtMaterials(0).dblQuantity = 5 Set udtMaterials(0).rngItem = Range("A100:D100") ReDim Preserve udtMaterials(1) udtMaterials(1).dblQuantity = 6 Set udtMaterials(1).rngItem = Range("A101:D101") MsgBox udtMaterials(0).rngItem.Address & vbCrLf & udtMaterials(0).dblQuantity MsgBox udtMaterials(1).rngItem.Address & vbCrLf & udtMaterials(1).dblQuantity End Sub -- HTH... Jim Thomlinson "RyanH" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array values with UBound | Excel Programming | |||
Array Ubound | Excel Programming | |||
UBound of multi-dimensional array? | Excel Programming | |||
Handling ubound on an uninitialised array | Excel Programming |