Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button on Userf
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button onUserf
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button on Userf
Just a thought, may not work. How about trying:
Private cmbCalculate_Click() If CheckBox1 = True Then var1 = 100 var2 = 0 Else var1 = 0 var2 = 200 End If If ComboBox.ListIndex = 1 Then var4 = 0 var3 = 300 Else var3 = 0 var4 = 400 End If End Sub Maybe I didn't understand the problem all the way? -- -SA "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button on U
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button on Userf
This should work for you! I took the liberty of highlighting the unlocked
cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable in Array = Empty each click of a Button on U
I have thought about something like that, but the code gets really messy that
way. I was hoping to find something more efficient. -- Cheers, Ryan "StumpedAgain" wrote: Just a thought, may not work. How about trying: Private cmbCalculate_Click() If CheckBox1 = True Then var1 = 100 var2 = 0 Else var1 = 0 var2 = 200 End If If ComboBox.ListIndex = 1 Then var4 = 0 var3 = 300 Else var3 = 0 var4 = 400 End If End Sub Maybe I didn't understand the problem all the way? -- -SA "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make array position A(12) into a variable A(12*n) ? | Excel Worksheet Functions | |||
array variable is empty? | Excel Programming | |||
Empty excel files when double click on them | Excel Discussion (Misc queries) | |||
Create floating button based on button click in menu | Excel Programming | |||
how do I make single character as a variable in an array | Excel Worksheet Functions |