Clearing Variable Values in Array using For...Next

[RyanH]

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

 

[Jim Thomlinson]

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

 

[Mike H]

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