Using strings, integers and stuff




I am making a userform macro, within this macro is a large amount of code as

Private Sub SubmitButton_Click()

With Worksheets("MacroData")

CC1 = UserForm1.CC1TextBox.Text
..Range("CC1_ Submitted").Formula = CC1

CC2 = UserForm1.CC2TextBox.Text
..Range("CC2_ Submitted").Formula = CC2

CC3 = UserForm1.CC3TextBox.Text
..Range("CC3_ Submitted").Formula = CC3

CC4 = UserForm1.CC4TextBox.Text
..Range("CC4_ Submitted").Formula = CC4

CC5 = UserForm1.CC5TextBox.Text
..Range("CC5_ Submitted").Formula = CC5

CC6 = UserForm1.CC6TextBox.Text
..Range("CC6_ Submitted").Formula = CC6

CC7 = UserForm1.CC7TextBox.Text
..Range("CC7_ Submitted").Formula = CC7

CC8 = UserForm1.CC8TextBox.Text
..Range("CC8_ Submitted").Formula = CC8

CC9 = UserForm1.CC9TextBox.Text
..Range("CC9_ Submitted").Formula = CC9

CC10 = UserForm1.CC10TextBox.Text
..Range("CC10_ Submitted").Formula = CC10

CC11 = UserForm1.CC11TextBox.Text
..Range("CC11_ Submitted").Formula = CC11

CC12 = UserForm1.CC12TextBox.Text
..Range("CC12_ Submitted").Formula = CC12

CC13 = UserForm1.CC13TextBox.Text
..Range("CC13_ Submitted").Formula = CC13

CC14 = UserForm1.CC14TextBox.Text
..Range("CC14_ Submitted").Formula = CC14

CC15 = UserForm1.CC15TextBox.Text
..Range("CC15_ Submitted").Formula = CC15

CC16 = UserForm1.CC16TextBox.Text
..Range("CC16_ Submitted").Formula = CC16

CC17 = UserForm1.CC17TextBox.Text
..Range("CC17_ Submitted").Formula = CC17

CC18 = UserForm1.CC18TextBox.Text
..Range("CC18_ Submitted").Formula = CC18

CC19 = UserForm1.CC19TextBox.Text
..Range("CC19_ Submitted").Formula = CC19

CC20 = UserForm1.CC20TextBox.Text
..Range("CC20_ Submitted").Formula = CC20

CC21 = UserForm1.CC21TextBox.Text
..Range("CC21_ Submitted").Formula = CC21

CC22 = UserForm1.CC22TextBox.Text
..Range("CC22_ Submitted").Formula = CC22

CC23 = UserForm1.CC23TextBox.Text
..Range("CC23_ Submitted").Formula = CC23

CC24 = UserForm1.CC24TextBox.Text
..Range("CC24_ Submitted").Formula = CC24

CC25 = UserForm1.CC25TextBox.Text
..Range("CC25_ Submitted").Formula = CC25

CC26 = UserForm1.CC26TextBox.Text
..Range("CC26_ Submitted").Formula = CC26

CC27 = UserForm1.CC27TextBox.Text
..Range("CC27_ Submitted").Formula = CC27

CC28 = UserForm1.CC28TextBox.Text
..Range("CC28_ Submitted").Formula = CC28

CC29 = UserForm1.CC29TextBox.Text
..Range("CC29_ Submitted").Formula = CC29

CC30 = UserForm1.CC30TextBox.Text
..Range("CC30_ Submitted").Formula = CC30

CC31 = UserForm1.CC31TextBox.Text
..Range("CC31_ Submitted").Formula = CC31

CC32 = UserForm1.CC32TextBox.Text
..Range("CC32_ Submitted").Formula = CC32

CC33 = UserForm1.CC33TextBox.Text
..Range("CC33_ Submitted").Formula = CC33

CC33 = UserForm1.CC33TextBox.Text
..Range("CC33_ Submitted").Formula = CC33

CC34 = UserForm1.CC34TextBox.Text
..Range("CC34_ Submitted").Formula = CC34

CC35 = UserForm1.CC35TextBox.Text
..Range("CC35_ Submitted").Formula = CC35

CC36 = UserForm1.CC36TextBox.Text
..Range("CC36_ Submitted").Formula = CC36

CC37 = UserForm1.CC37TextBox.Text
..Range("CC37_ Submitted").Formula = CC37

CC38 = UserForm1.CC38TextBox.Text
..Range("CC38_ Submitted").Formula = CC38

CC39 = UserForm1.CC39TextBox.Text
..Range("CC39_ Submitted").Formula = CC39

CC40 = UserForm1.CC40TextBox.Text
..Range("CC40_ Submitted").Formula = CC40

CC41 = UserForm1.CC41TextBox.Text
..Range("CC41_ Submitted").Formula = CC41

CC42 = UserForm1.CC42TextBox.Text
..Range("CC42_ Submitted").Formula = CC42

CC42 = UserForm1.CC42TextBox.Text
..Range("CC42_ Submitted").Formula = CC42

CC43 = UserForm1.CC43TextBox.Text
..Range("CC43_ Submitted").Formula = CC43

CC44 = UserForm1.CC44TextBox.Text
..Range("CC44_ Submitted").Formula = CC44

CC45 = UserForm1.CC45TextBox.Text
..Range("CC45_ Submitted").Formula = CC45


End With

End Sub

what i would like to do as this amount of repetition coding appears a number
of times throughout the macro is to shorten it using

Dim i as integer

For i = 1 to 45


but i don't know how to do this putting (i) into the text after the CC so
that say when
i = 5
using CC(i) (this is wrong i know, this is my question)
i get CC5





Tom Ogilvy

Private Sub SubmitButton_Click()
With Worksheets("MacroData")
for i = 1 to 45
.Range("CC" & i & "_ Submitted").Formula = _
UserForm1.Controls("CC" & i & "TextBox").Text
End with
End Sub




thanks tom i will try this, the macro is working ok, will just make it easier
to look at in VB and to change/manage and hopefully use less stackspace,



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads