PDA

View Full Version : Solved: loop varaible



white_flag
09-07-2010, 07:19 AM
Hello

because my VBA skills are to minimum I do not know how.. or if it is possible to declare an "loop variable". The scenario is:

I have some checkboxes. if the checkbox is checked then the string from an text box will be converted in number by CDbl function, then those numbers to be assign to an "loop variable". How can I solve this problem?


Dim i as String
For i = 31 To 40
If Me.Controls("CheckBox" & i - 30) = True Then
If Me.Controls("TextBox" & i).Text = "" Then Me.Controls("TextBox" & i).Text = 0
End If
formula & i = CDbl(Me.Controls("TextBox" & i).Text) <<< this is not working
Next

Simon Lloyd
09-07-2010, 07:51 AM
You have i declared as a string but use it as an integer or long, so formula & i would be e.g "formula31" or depending what the value of i is at the time, note the quotes, thats to show you it would be all text.

Provide a sample workbook with a before and after view and an explanation of what you are trying to achieve and we can help you with that.

Bob Phillips
09-07-2010, 08:09 AM
If it is the loop variable, do you just want



Dim i As String
For i = 31 To 40
If Me.Controls("CheckBox" & i - 30) = True Then
If Me.Controls("TextBox" & i).Text = "" Then Me.Controls("TextBox" & i).Text = 0
End If
i = CDbl(Me.Controls("TextBox" & i).Text)
Next

white_flag
09-07-2010, 08:12 AM
ok :) please, look in attachment (it is not complete), it is just an excel sketch

Bob Phillips
09-07-2010, 08:20 AM
Private Sub CommandButton1_Click()
Dim Formula As Double
Dim i As Long

With ComboBox1
If .ListIndex > -1 Then
MsgBox "You selected " & .List(.ListIndex, 0) & " " & .List(.ListIndex, 1)
End If
End With

calculation_txtbx
For i = 31 To 40
If Me.Controls("CheckBox" & i - 30) = True Then
If Me.Controls("TextBox" & i).Text = "" Then Me.Controls("TextBox" & i).Text = 0
Formula = Formula + CDbl(Me.Controls("TextBox" & i).Text)
End If
Next

Sheet1.Range("C2:W10").Value = Formula
End Sub

white_flag
09-07-2010, 08:21 AM
thx, xld for the idea.. but I need the "i" as integer otherwise it is not working (or it is acting really strange).

Bob Phillips
09-07-2010, 08:28 AM
You don't need it an d you certainly cannot use it like that. Variables cannot be dynamically referenced as you are trying to do.

What is wrong with what I gave you? It does exactly what you code seemed to be trying to do.

white_flag
09-07-2010, 08:39 AM
ok ..I like to make an sum from textboxes and I think that was an simply way to declare the variable via an "loop-er"

in the end to have like this
Formula = txtBox1+txtBox2+txtBox3+txtBox4+txtBox5 ..etc till 10
then this formula to be dropped in excel.

Bob Phillips
09-07-2010, 08:48 AM
That is exactly what my code does, makes that sum.

white_flag
09-07-2010, 08:55 AM
correct :) it is working very nice ..thank you xld ..have a nice evening (or morning) ..here it is evening