Excel VBA incrementing variable in for loop

Question

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?

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.

Any help is greatly appreciated!

Thanks!

just wondering if range("B" & addatrow - 1 + i), shouldn't be range("B" & (addatrow - 1 + i)) ?? Also you could use Cells (addatrow - 1 + i,2) — Patrick Lepelletier
– Patrick Lepelletier, Commented Nov 7, 2014 at 18:20

SkyMaster · Accepted Answer · 2014-11-07 00:25:55Z

4

Try this:

Note that nameForm must be the name of your form, With Me seems not to work.

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

answered Nov 7, 2014 at 0:25

SkyMaster

1,3231 gold badge8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Mixon Over a year ago

This worked! turned 90+ lines of code to about 10! Thank you!

Patrick Lepelletier Over a year ago

@Jose, Me works as long as you are in the userform code's page ; for any module sub's code you need to either use the global name of the Form, or to pass it (from the Form to a module) in an argument to the sub like this : call Mysub(arg1,arg2,Me) , and then in the module : sub Mysub (byval arg1 as Long, byval arg2 as String, byref Form as UserForm). Note the Byref is there to save any changes to the UserForm.

Patrick Lepelletier · Accepted Answer · 2014-11-07 18:38:43Z

1

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

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

answered Nov 7, 2014 at 18:38

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

1 Comment

Patrick Lepelletier Over a year ago

I know using a VBA array and writing it in one swoop is a big overkill, but i like things fast ^^

Robin Hartland · Accepted Answer · 2014-11-07 00:11:59Z

0

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)

answered Nov 7, 2014 at 0:11

Robin Hartland

3362 silver badges12 bronze badges

1 Comment

Patrick Lepelletier Over a year ago

why loop through the controls if you already know the name of it ? Double looping makes no sense. also, in VBA you don't need to convert to a string when you write "ffff" & i , its automaticaly done by the application.

Collectives™ on Stack Overflow

Excel VBA incrementing variable in for loop

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related