vba Excel VBA在for循环中递增变量

Excel VBA incrementing variable in for loop

提问by Brian Mixon

I'm still a new learner of VBA programming, and I've come across an issue that I can't seem to find a solution to. I am trying to create a workbook to handle recipes. I am calling a user form used to add a recipe in a macro with several text box inputs (~96 of them). When I've input all ingredients, quantity, units and press the OK button, I want it to write what's in the user form to the worksheet. All text boxes are named ending with a number in ascending order (e.g. .txtIngredient1, .txtIngredient2, ...). Is it possible to use a for loop to copy the Me.txtIngredientX.Value to a range on a sheet?

我仍然是 VBA 编程的新学习者，我遇到了一个我似乎无法找到解决方案的问题。我正在尝试创建一个工作簿来处理食谱。我正在调用一个用于在宏中添加配方的用户表单，其中包含多个文本框输入（约 96 个）。当我输入所有成分、数量、单位并按下 OK 按钮后，我希望它将用户表单中的内容写入工作表。所有文本框都以数字升序命名（例如 .txtIngredient1、.txtIngredient2、...）。是否可以使用 for 循环将 Me.txtIngredientX.Value 复制到工作表上的范围？

A snippet of my current code:

我当前代码的片段：

Sheets("Recipes").Range("B" & addatrow + 0) = Me.txtIngredient1.Value
Sheets("Recipes").Range("B" & addatrow + 1) = Me.txtIngredient2.Value
Sheets("Recipes").Range("B" & addatrow + 2) = Me.txtIngredient3.Value
Sheets("Recipes").Range("B" & addatrow + 3) = Me.txtIngredient4.Value
Sheets("Recipes").Range("B" & addatrow + 4) = Me.txtIngredient5.Value
Sheets("Recipes").Range("B" & addatrow + 5) = Me.txtIngredient6.Value
Sheets("Recipes").Range("B" & addatrow + 6) = Me.txtIngredient7.Value
Sheets("Recipes").Range("B" & addatrow + 7) = Me.txtIngredient8.Value
Sheets("Recipes").Range("B" & addatrow + 8) = Me.txtIngredient9.Value
Sheets("Recipes").Range("B" & addatrow + 9) = Me.txtIngredient10.Value
Sheets("Recipes").Range("B" & addatrow + 10) = Me.txtIngredient11.Value
Sheets("Recipes").Range("B" & addatrow + 11) = Me.txtIngredient12.Value
Sheets("Recipes").Range("B" & addatrow + 12) = Me.txtIngredient13.Value

I have tried the only thing I know to do which is something like this:

我已经尝试过我唯一知道要做的事情是这样的：

for i = 1 to 32
    Sheets("Recipes").Range("B" & addatrow - 1 + i) = me.txtIngredient & i.value

which does not work. Note: addatrow is a variable in the macro that dictates where the next recipe should be inserted.

这不起作用。注意：addatrow 是宏中的一个变量，它指示应该插入下一个配方的位置。

Any help is greatly appreciated!

任何帮助是极大的赞赏！

Thanks!

谢谢！

回答by SkyMaster

Try this:

尝试这个：

Note that nameFormmust be the name of your form, With Meseems not to work.

请注意，nameForm必须是您的表单名称，WithMe似乎不起作用。

for i = 1 to 32
    Sheets("Recipes").Range("B" & addatrow - 1 + i) = nameForm.Controls("txtIngredient" & i).Value

回答by Patrick Lepelletier

this code must be linked to the Ok button, inside the UserForm's code page :

此代码必须链接到用户窗体代码页中的 Ok 按钮：

Sub Button_OK_Click 'Assuming the 'Ok' button is called Button_Ok on the userform

dim DATA () 'automatically declared as Variant, need to be Variant.
dim Sh as worksheet
dim i& ' declared as Long , this is our looping variable
redim DATA (1 to 96, 1 to 1) 'a one colone array with 96 rows
set Sh = thisworkbook.Sheets("Recipes")

with Me
    for i = 1 to 96
        DATA (i,1) = .Controls("txtIngredient" & i).Value
    next i
end with

with application
    .screenupdating = false
    .enableevents = false
    .calculation = XlManual
end with


with Sh
    .Range( .cells(addatrow,2) , .cells (addatrow+95 , 2) ).value2 = DATA 'write it to the sheet in a single line
end with

erase DATA
Set Sh = Nothing

with application
    .screenupdating = true
    .enableevents = true
    .calculation = XlAutomatic
end with

end sub

回答by Robin Hartland

How about:

怎么样：

for i as integer = 1 to 32
    for each c as control in me.controls
        if typeof(c) is textbox andAlso c.name = "txtIngredient" & i.tostring then
            Sheets("Recipes").Range("B" & addatrow - 1 + i) = c.value
            exit for
        end if
    next c
next i

Good question, I have often wanted to do this kind of thing myself :)

好问题，我经常想自己做这种事情:)

(That's VB.NET code, don't know how much of that will work in VBA, hope it works)

（那是 VB.NET 代码，不知道有多少会在 VBA 中起作用，希望它起作用）