I may not be coding this right. This is what I have and I am getting an
error 'Subscript out of Range' indicated below. Also, if I know myArray
always contains variables with Data Type "Single" and always has an UBound or
13, why can't I use: Dim myArray(13) As Single?


Dim myArray As Variant

' array of row numbers determined by procedure cmbCalculate_Click
myArray = Array(var1, var2, var3, var4)

Erase myArray

' calculate variables in myArray
Call cmbCalculate_Click

' adds text from Sheet1 to Sheet2
InputRow = 6

ERROR= For i = LBound(myArray) To UBound(myArray)
If Not myArray(i) = 0 Then
Sheets("Sheet1").Range("A" & InputRow & ":D" &
InputRow).Value = _
Sheets("Sheet2").Range("A" & myArray(i) & ":D" &
myArray(i)).Value
InputRow = InputRow + 2
End If
Next i

End Sub

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
Else
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var3 = 300
Else
var4 = 400
End If
End Sub
--
Cheers,
Ryan


"Leith Ross" wrote:

On Jul 28, 9:52 am, RyanH wrote:
The following code is an example of what my actual code is (my actual code is
too lengthy to post). myArray will always contain 4 variables. I need to
reset each variable in myArray equal to 0 each time Sub Test() is called. As
you can see I have to declare the variables in the Declarations section to
make them available to both procedures. By doing this unwanted text is
copied from Sheet1 to Sheet2.

For example, in my UserForm say the user fires the Test_Click Event and
CheckBox1 = True and ComboBox.ListIndex = 1, thus var1=100, var2=0, var3=300,
var4=0 and the UserFrom remains Loaded. So far so good! The problem happens
when the user may fire the Test_Click Event again when CheckBox1 = False and
ComboBox.ListIndex = 3, thus var1=100, var2=200, var3=300, var4=400. This is
no good, because now all four variables have data other than = 0. The
variables should show like this: var1=0, var2=200, var3=0, var4=400.

So how do I reset the variables values without destroying myArray? I don't
think I could use Erase or ReDim, because this would destroy the Array,
right? I thought about just looping thru myArray and make each variable =
Empty, but I figured there would be a more efficient way. I hope this is
clear.

Option Explicit

Dim var1 As Single
Dim var2 As Single
Dim var3 As Single
Dim var4 As Single

Private Sub cmbTest_Click()

Dim myArray As Variant

' array of row numbers determined by procedure cmbCalculate_Click
myArray = Array(var1, var2, var3, var4)

' calculate variables in myArray
Call cmbCalculate_Click

' adds text from Sheet1 to Sheet2
InputRow = 6
For i = LBound(myArray) To UBound(myArray)
If Not myArray(i) = 0 Then
Sheets("Sheet1").Range("A" & InputRow & ":D" &
InputRow).Value = _
Sheets("Sheet2").Range("A" & myArray(i) & ":D" &
myArray(i)).Value
InputRow = InputRow + 2
End If
Next i

End Sub

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
Else
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var3 = 300
Else
var4 = 400
End If

End Sub

--
Cheers,
Ryan

Hello Ryan,

Erase will set all the elements of a numeric array to zero and a
string array's elements to an empty string "". The array will not be
destroyed.

Sincerely,
Leith Ross