| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substitute a variable in a loop
I have a multiple text boxes in a form (excel 2000) that are named:
cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
On a userform? dim iCtr as long for ictr = 1 to 3 'same number as your textboxes. activecell.offset(0,ictr).value _ = me.controls("cmake" & format(ictr,"00")).value next ictr TheMike wrote: I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
You need the item in the control form; something like this: For i = 1 To 3 ActiveCell.Offset(0, i) = UserForm1.Controls.Item(i).Value Next i -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "TheMike" wrote: I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
For i = 1 To 3 ActiveCell.Offset(0, 2) = Me.Controls("cMake" & Format(i, "00")).Text Next -- __________________________________ HTH Bob "TheMike" wrote in message ... I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
Dim i As Long For i = 1 To 3 ActiveCell.Offset(0, i) = _ Me.Controls("cMake" & Right$("0" & i, 2)).Text Next Regards, Peter T "TheMike" wrote in message ... I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
Thanks Dave,
Your solution came closest, problem solved, thank you very much! Can you explain to me whats the "me." part in of "me.controls("cmake" & format(ictr,"00")).value" stands for? Regards Mike On 6 jan, 22:30, Dave Peterson wrote: On a userform? dim iCtr as long for ictr = 1 to 3 'same number as your textboxes. * activecell.offset(0,ictr).value _ * * *= me.controls("cmake" & format(ictr,"00")).value next ictr TheMike wrote: I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 * * * ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
Me refers to the containing class, the userform in this case.
Me in worksheet event code refers to the worksheet, in workbook event code refers to the workbook. -- __________________________________ HTH Bob "TheMike" wrote in message ... Thanks Dave, Your solution came closest, problem solved, thank you very much! Can you explain to me whats the "me." part in of "me.controls("cmake" & format(ictr,"00")).value" stands for? Regards Mike On 6 jan, 22:30, Dave Peterson wrote: On a userform? dim iCtr as long for ictr = 1 to 3 'same number as your textboxes. activecell.offset(0,ictr).value _ = me.controls("cmake" & format(ictr,"00")).value next ictr TheMike wrote: I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
I see you did it again <g
When I posted there were no others, but now Dave's is timed at nearly 40 minutes earlier, Michael's at nearly 20. How odd! -- __________________________________ HTH Bob "Peter T" <peter_t@discussions wrote in message ... Dim i As Long For i = 1 To 3 ActiveCell.Offset(0, i) = _ Me.Controls("cMake" & Right$("0" & i, 2)).Text Next Regards, Peter T "TheMike" wrote in message ... I have a multiple text boxes in a form (excel 2000) that are named: cMake01, cMake02 cMake03 etc.. I want to save the info from my form to a worksheet. instead of: ActiveCell.Offset(0, 1) = cMake01.Value ActiveCell.Offset(0, 2) = cMake02.Value ActiveCell.Offset(0, 3) = cMake03.Value I want to do it in a loop: For i = 1 To 3 ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value" Next But now the values in my worksheet a "cMake01.Value"....."cMake03.Value" What is the correct syntax to save the content of the text box and not the name? regards Mike |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
"Bob Phillips" wrote in message
I see you did it again <g When I posted there were no others, but now Dave's is timed at nearly 40 minutes earlier, Michael's at nearly 20. How odd! Yep, seems like it, so not just me then. I know there's often a delay up to 20 minutes but normally not hours (in my case with two posts last night). Regards, Peter T |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute a variable in a loop
I normally see them within 5 minutes, and I do check as I try to avoid
posting an answer that someone has already given. But I just didn't see these, and Dave's is almost identical to mine. -- __________________________________ HTH Bob "Peter T" <peter_t@discussions wrote in message ... "Bob Phillips" wrote in message I see you did it again <g When I posted there were no others, but now Dave's is timed at nearly 40 minutes earlier, Michael's at nearly 20. How odd! Yep, seems like it, so not just me then. I know there's often a delay up to 20 minutes but normally not hours (in my case with two posts last night). Regards, Peter T |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop with variable name? | Excel Discussion (Misc queries) | |||
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
| loop for-next with variable end | Excel Programming | |||
| SUBSTITUTE formula -- variable spacing between parameters?? | Excel Worksheet Functions | |||
| For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Linear Mode
