Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Variable Values in Array using For...Next
I would like to make all variable values in the two Arrays below = 0. Note:
All my variables listed in the Arrays are declared in the Declarations section at the top of the UserForm Module. When I run this code it doesn't work the way I want it too. This is an example in the immediate window (which is not what I want): currently: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 32 it should read: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 0 Why? Private Sub cmbJobCosting_Click() Dim myBOM_Des As Variant Dim myBOM_Qty As Variant ' Establish Array to CLEAR myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row, Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _ Vinyl2_Row, Vinyl3_Row, Vinyl4_Row, ClearVinyl_Row, Ink_Row, Plastic_Row) ' Establish Array to CLEAR myBOM_Qty = Array(AlumFace_Qty, Texture_Qty, Primer_Qty, Paint1_Qty, Paint2_Qty, Paint3_Qty, Paint4_Qty, Vinyl1_Qty, _ Vinyl2_Qty, Vinyl3_Qty, Vinyl4_Qty, ClearVinyl_Qty, Ink_Qty, Plastic_Qty) ' resets all variables to Empty in Arrays If UBound(myBOM_Des) < UBound(myBOM_Qty) Then MsgBox "The variable count in myBOM_Des and myBOM_Qty does NOT equal. Call VBA Developer.", vbCritical Exit Sub Else For i = LBound(myBOM_Des) To UBound(myBOM_Des) myBOM_Des(i) = 0 myBOM_Qty(i) = 0 Next i End If End Sub -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Variable Values in Array using For...Next
Your code worked for me... taht being said this is a bit shorter and might be
worth a try... Dim myBOM_Des As Variant Dim myBOM_Qty As Variant Dim i As Long ' Establish Array to CLEAR myBOM_Des = Array(1, 2, 3, 4, 5) myBOM_Qty = Array(10, 20, 30, 40, 50) ' resets all variables to Empty in Arrays If UBound(myBOM_Des) < UBound(myBOM_Qty) Then MsgBox "The variable count in myBOM_Des and myBOM_Qty does NOT equal. Call VBA Developer.", vbCritical Exit Sub Else ReDim myBOM_Des(UBound(myBOM_Des)) ReDim myBOM_Qty(UBound(myBOM_Des)) End If End Sub -- HTH... Jim Thomlinson "RyanH" wrote: I would like to make all variable values in the two Arrays below = 0. Note: All my variables listed in the Arrays are declared in the Declarations section at the top of the UserForm Module. When I run this code it doesn't work the way I want it too. This is an example in the immediate window (which is not what I want): currently: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 32 it should read: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 0 Why? Private Sub cmbJobCosting_Click() Dim myBOM_Des As Variant Dim myBOM_Qty As Variant ' Establish Array to CLEAR myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row, Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _ Vinyl2_Row, Vinyl3_Row, Vinyl4_Row, ClearVinyl_Row, Ink_Row, Plastic_Row) ' Establish Array to CLEAR myBOM_Qty = Array(AlumFace_Qty, Texture_Qty, Primer_Qty, Paint1_Qty, Paint2_Qty, Paint3_Qty, Paint4_Qty, Vinyl1_Qty, _ Vinyl2_Qty, Vinyl3_Qty, Vinyl4_Qty, ClearVinyl_Qty, Ink_Qty, Plastic_Qty) ' resets all variables to Empty in Arrays If UBound(myBOM_Des) < UBound(myBOM_Qty) Then MsgBox "The variable count in myBOM_Des and myBOM_Qty does NOT equal. Call VBA Developer.", vbCritical Exit Sub Else For i = LBound(myBOM_Des) To UBound(myBOM_Des) myBOM_Des(i) = 0 myBOM_Qty(i) = 0 Next i End If End Sub -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Variable Values in Array using For...Next
RyanH,
If you are going to exit the sub why are you bothering to reset the aray? Anyway addthe couple of lines as the last 4 lines of you sub and you will see that they are all reset to 0 For i = LBound(myBOM_Des) To UBound(myBOM_Des) Debug.Print "myBOM_Des" & i & " " & myBOM_Des(i) Debug.Print "myBOM_Qty" & i & " " & myBOM_Qty(i) Next i Mike "RyanH" wrote: I would like to make all variable values in the two Arrays below = 0. Note: All my variables listed in the Arrays are declared in the Declarations section at the top of the UserForm Module. When I run this code it doesn't work the way I want it too. This is an example in the immediate window (which is not what I want): currently: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 32 it should read: ?myBOM_Qty(0) = 0 ?AlumFace_Qty = 0 Why? Private Sub cmbJobCosting_Click() Dim myBOM_Des As Variant Dim myBOM_Qty As Variant ' Establish Array to CLEAR myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row, Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _ Vinyl2_Row, Vinyl3_Row, Vinyl4_Row, ClearVinyl_Row, Ink_Row, Plastic_Row) ' Establish Array to CLEAR myBOM_Qty = Array(AlumFace_Qty, Texture_Qty, Primer_Qty, Paint1_Qty, Paint2_Qty, Paint3_Qty, Paint4_Qty, Vinyl1_Qty, _ Vinyl2_Qty, Vinyl3_Qty, Vinyl4_Qty, ClearVinyl_Qty, Ink_Qty, Plastic_Qty) ' resets all variables to Empty in Arrays If UBound(myBOM_Des) < UBound(myBOM_Qty) Then MsgBox "The variable count in myBOM_Des and myBOM_Qty does NOT equal. Call VBA Developer.", vbCritical Exit Sub Else For i = LBound(myBOM_Des) To UBound(myBOM_Des) myBOM_Des(i) = 0 myBOM_Qty(i) = 0 Next i End If End Sub -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Series values from an array variable | Excel Programming | |||
Clearing all values in a User Defined Type variable | Excel Programming | |||
Getting values from a variable sized range into an array | Excel Programming | |||
vba clearing out values stored in array | Excel Programming |