Consulting

Results 1 to 2 of 2

Thread: can this be simplified with a loop?

  1. 01-11-2012, 09:25 PM #1
    JordanGoodch
    JordanGoodch is offline
    VBAX Regular
    Joined
    Jul 2010
    Posts
    10
    Location

    can this be simplified with a loop?

    Is there a way in which to simplify this code with loops.
    There a two sections one that clears all of the text boxes on the sheet and the second section is used to save the data in the text boxes onto the sheet.

    [vba]
    Private Sub UserForm_Open()
    ComboBox1.Clear
    TextBox2.Clear
    TextBox3.Clear
    TextBox4.Clear
    TextBox5.Clear
    TextBox6.Clear
    TextBox7.Clear
    TextBox8.Clear
    TextBox9.Clear
    TextBox10.Clear
    TextBox11.Clear
    TextBox12.Clear
    TextBox13.Clear
    TextBox14.Clear
    TextBox15.Clear
    TextBox16.Clear
    TextBox17.Clear
    TextBox18.Clear
    TextBox19.Clear
    TextBox20.Clear
    TextBox21.Clear
    TextBox22.Clear
    TextBox23.Clear
    TextBox24.Clear
    TextBox25.Clear
    TextBox26.Clear
    TextBox27.Clear
    TextBox28.Clear
    TextBox29.Clear
    TextBox30.Clear
    TextBox31.Clear
    TextBox32.Clear
    TextBox33.Clear
    TextBox34.Clear
    TextBox35.Clear
    TextBox36.Clear
    TextBox37.Clear
    TextBox38.Clear
    TextBox39.Clear
    TextBox40.Clear
    TextBox41.Clear
    TextBox42.Clear
    TextBox43.Clear
    TextBox44.Clear
    TextBox45.Clear
    TextBox46.Clear
    TextBox47.Clear
    TextBox48.Clear
    TextBox49.Clear
    TextBox50.Clear
    TextBox51.Clear
    TextBox52.Clear
    TextBox53.Clear
    TextBox54.Clear
    TextBox55.Clear
    TextBox56.Clear
    TextBox57.Clear
    TextBox58.Clear
    TextBox59.Clear
    TextBox60.Clear
    TextBox61.Clear
    TextBox62.Clear
    TextBox63.Clear
    TextBox64.Clear
    TextBox65.Clear
    TextBox66.Clear
    TextBox67.Clear
    TextBox68.Clear
    TextBox69.Clear
    TextBox70.Clear
    TextBox71.Clear
    TextBox72.Clear
    TextBox73.Clear
    TextBox74.Clear
    TextBox75.Clear
    TextBox76.Clear
    TextBox77.Clear
    TextBox78.Clear
    TextBox79.Clear
    TextBox80.Clear
    TextBox81.Clear
    TextBox82.Clear
    TextBox83.Clear
    TextBox84.Clear
    TextBox85.Clear
    TextBox86.Clear
    TextBox87.Clear
    TextBox88.Clear
    TextBox89.Clear
    TextBox90.Clear
    TextBox91.Clear
    TextBox92.Clear
    TextBox93.Clear
    TextBox94.Clear
    TextBox95.Clear
    TextBox96.Clear
    TextBox97.Clear
    TextBox98.Clear
    TextBox99.Clear
    TextBox100.Clear
    TextBox101.Clear
    TextBox102.Clear
    TextBox103.Clear
    TextBox104.Clear
    TextBox105.Clear
    TextBox106.Clear
    TextBox107.Clear
    TextBox108.Clear
    TextBox109.Clear
    TextBox110.Clear
    TextBox111.Clear
    TextBox112.Clear
    TextBox113.Clear
    TextBox114.Clear
    TextBox115.Clear
    TextBox116.Clear
    TextBox117.Clear
    TextBox118.Clear
    TextBox119.Clear
    TextBox120.Clear
    TextBox121.Clear
    TextBox122.Clear
    TextBox123.Clear
    TextBox124.Clear
    TextBox125.Clear
    TextBox126.Clear
    TextBox127.Clear
    TextBox128.Clear
    TextBox129.Clear
    TextBox130.Clear
    TextBox131.Clear
    TextBox132.Clear
    TextBox133.Clear
    TextBox134.Clear
    TextBox135.Clear
    TextBox136.Clear
    TextBox137.Clear
    TextBox138.Clear
    TextBox139.Clear
    TextBox140.Clear
    TextBox141.Clear
    TextBox142.Clear
    TextBox143.Clear
    TextBox144.Clear
    TextBox145.Clear
    TextBox146.Clear
    TextBox147.Clear
    TextBox148.Clear
    TextBox149.Clear
    TextBox150.Clear
    TextBox151.Clear
    TextBox152.Clear
    TextBox153.Clear
    End Sub
    [/vba]


    [vba]
    Private Sub CommandButton1_Click()
    Dim I As Long
    Dim X As Long
    X = Worksheets("Export").Range("A375").End(xlUp).Row + 1
    I = Worksheets("Stockpile").Range("A375").End(xlUp).Row + 1


    Worksheets("Export").Cells(X, 1) = dt
    Worksheets("Export").Cells(X, 2) = Me.TextBox2.Value
    Worksheets("Export").Cells(X, 3) = Me.TextBox3.Value
    Worksheets("Export").Cells(X, 4) = Me.ComboBox1.Value

    Worksheets("Stockpile").Cells(I, 1) = dt
    Worksheets("Stockpile").Cells(I, 2) = Me.TextBox2.Value
    Worksheets("Stockpile").Cells(I, 3) = Me.TextBox3.Value
    Worksheets("Stockpile").Cells(I, 4) = Me.ComboBox1.Value

    Worksheets("Stockpile").Cells(I, 5) = Me.TextBox4.Value
    Worksheets("Stockpile").Cells(I, 6) = Me.TextBox5.Value
    Worksheets("Stockpile").Cells(I, 7) = Me.TextBox6.Value
    Worksheets("Stockpile").Cells(I, 8) = Me.TextBox7.Value
    Worksheets("Stockpile").Cells(I, 9) = Me.TextBox8.Value
    Worksheets("Stockpile").Cells(I, 10) = Me.TextBox9.Value
    Worksheets("Stockpile").Cells(I, 11) = Me.TextBox10.Value
    Worksheets("Stockpile").Cells(I, 12) = Me.TextBox11.Value
    Worksheets("Stockpile").Cells(I, 13) = Me.TextBox12.Value
    Worksheets("Stockpile").Cells(I, 14) = Me.TextBox13.Value
    Worksheets("Stockpile").Cells(I, 15) = Me.TextBox14.Value
    Worksheets("Stockpile").Cells(I, 16) = Me.TextBox15.Value
    Worksheets("Stockpile").Cells(I, 17) = Me.TextBox16.Value
    Worksheets("Stockpile").Cells(I, 18) = Me.TextBox17.Value
    Worksheets("Stockpile").Cells(I, 19) = Me.TextBox18.Value
    Worksheets("Stockpile").Cells(I, 20) = Me.TextBox19.Value
    Worksheets("Stockpile").Cells(I, 21) = Me.TextBox20.Value
    Worksheets("Stockpile").Cells(I, 22) = Me.TextBox21.Value
    Worksheets("Stockpile").Cells(I, 23) = Me.TextBox22.Value
    Worksheets("Stockpile").Cells(I, 24) = Me.TextBox23.Value
    Worksheets("Stockpile").Cells(I, 25) = Me.TextBox24.Value
    Worksheets("Stockpile").Cells(I, 26) = Me.TextBox25.Value
    Worksheets("Stockpile").Cells(I, 27) = Me.TextBox26.Value
    Worksheets("Stockpile").Cells(I, 28) = Me.TextBox27.Value
    Worksheets("Stockpile").Cells(I, 29) = Me.TextBox28.Value
    Worksheets("Stockpile").Cells(I, 30) = Me.TextBox29.Value
    Worksheets("Stockpile").Cells(I, 31) = Me.TextBox30.Value
    Worksheets("Stockpile").Cells(I, 32) = Me.TextBox31.Value
    Worksheets("Stockpile").Cells(I, 33) = Me.TextBox32.Value
    Worksheets("Stockpile").Cells(I, 34) = Me.TextBox33.Value
    Worksheets("Stockpile").Cells(I, 35) = Me.TextBox34.Value
    Worksheets("Stockpile").Cells(I, 36) = Me.TextBox35.Value
    Worksheets("Stockpile").Cells(I, 37) = Me.TextBox36.Value
    Worksheets("Stockpile").Cells(I, 38) = Me.TextBox37.Value
    Worksheets("Stockpile").Cells(I, 39) = Me.TextBox38.Value
    Worksheets("Stockpile").Cells(I, 40) = Me.TextBox39.Value
    Worksheets("Stockpile").Cells(I, 41) = Me.TextBox40.Value
    Worksheets("Stockpile").Cells(I, 42) = Me.TextBox41.Value
    Worksheets("Stockpile").Cells(I, 43) = Me.TextBox42.Value
    Worksheets("Stockpile").Cells(I, 44) = Me.TextBox43.Value
    Worksheets("Stockpile").Cells(I, 45) = Me.TextBox44.Value
    Worksheets("Stockpile").Cells(I, 46) = Me.TextBox45.Value
    Worksheets("Stockpile").Cells(I, 47) = Me.TextBox46.Value
    Worksheets("Stockpile").Cells(I, 48) = Me.TextBox47.Value
    Worksheets("Stockpile").Cells(I, 49) = Me.TextBox48.Value
    Worksheets("Stockpile").Cells(I, 50) = Me.TextBox49.Value
    Worksheets("Stockpile").Cells(I, 51) = Me.TextBox50.Value
    Worksheets("Stockpile").Cells(I, 52) = Me.TextBox51.Value
    Worksheets("Stockpile").Cells(I, 53) = Me.TextBox52.Value
    Worksheets("Stockpile").Cells(I, 54) = Me.TextBox53.Value
    Worksheets("Stockpile").Cells(I, 55) = Me.TextBox54.Value
    Worksheets("Stockpile").Cells(I, 56) = Me.TextBox55.Value
    Worksheets("Stockpile").Cells(I, 57) = Me.TextBox56.Value
    Worksheets("Stockpile").Cells(I, 58) = Me.TextBox57.Value
    Worksheets("Stockpile").Cells(I, 59) = Me.TextBox58.Value
    Worksheets("Stockpile").Cells(I, 60) = Me.TextBox59.Value
    Worksheets("Stockpile").Cells(I, 61) = Me.TextBox60.Value
    Worksheets("Stockpile").Cells(I, 62) = Me.TextBox61.Value
    Worksheets("Stockpile").Cells(I, 63) = Me.TextBox62.Value
    Worksheets("Stockpile").Cells(I, 64) = Me.TextBox63.Value
    Worksheets("Stockpile").Cells(I, 65) = Me.TextBox64.Value
    Worksheets("Stockpile").Cells(I, 66) = Me.TextBox65.Value
    Worksheets("Stockpile").Cells(I, 67) = Me.TextBox66.Value
    Worksheets("Stockpile").Cells(I, 68) = Me.TextBox67.Value
    Worksheets("Stockpile").Cells(I, 69) = Me.TextBox68.Value
    Worksheets("Stockpile").Cells(I, 70) = Me.TextBox69.Value
    Worksheets("Stockpile").Cells(I, 71) = Me.TextBox70.Value
    Worksheets("Stockpile").Cells(I, 72) = Me.TextBox71.Value
    Worksheets("Stockpile").Cells(I, 73) = Me.TextBox72.Value
    Worksheets("Stockpile").Cells(I, 74) = Me.TextBox73.Value
    Worksheets("Stockpile").Cells(I, 75) = Me.TextBox74.Value
    Worksheets("Stockpile").Cells(I, 76) = Me.TextBox75.Value
    Worksheets("Stockpile").Cells(I, 77) = Me.TextBox76.Value
    Worksheets("Stockpile").Cells(I, 78) = Me.TextBox77.Value
    Worksheets("Stockpile").Cells(I, 79) = Me.TextBox78.Value
    Worksheets("Stockpile").Cells(I, 80) = Me.TextBox79.Value
    Worksheets("Stockpile").Cells(I, 81) = Me.TextBox80.Value
    Worksheets("Stockpile").Cells(I, 82) = Me.TextBox81.Value
    Worksheets("Stockpile").Cells(I, 83) = Me.TextBox82.Value
    Worksheets("Stockpile").Cells(I, 84) = Me.TextBox83.Value
    Worksheets("Stockpile").Cells(I, 85) = Me.TextBox84.Value
    Worksheets("Stockpile").Cells(I, 86) = Me.TextBox85.Value
    Worksheets("Stockpile").Cells(I, 87) = Me.TextBox86.Value
    Worksheets("Stockpile").Cells(I, 88) = Me.TextBox87.Value
    Worksheets("Stockpile").Cells(I, 89) = Me.TextBox88.Value
    Worksheets("Stockpile").Cells(I, 90) = Me.TextBox89.Value
    Worksheets("Stockpile").Cells(I, 91) = Me.TextBox90.Value
    Worksheets("Stockpile").Cells(I, 92) = Me.TextBox91.Value
    Worksheets("Stockpile").Cells(I, 93) = Me.TextBox92.Value
    Worksheets("Stockpile").Cells(I, 94) = Me.TextBox93.Value
    Worksheets("Stockpile").Cells(I, 95) = Me.TextBox94.Value
    Worksheets("Stockpile").Cells(I, 96) = Me.TextBox95.Value
    Worksheets("Stockpile").Cells(I, 97) = Me.TextBox96.Value
    Worksheets("Stockpile").Cells(I, 98) = Me.TextBox97.Value
    Worksheets("Stockpile").Cells(I, 99) = Me.TextBox98.Value
    Worksheets("Stockpile").Cells(I, 100) = Me.TextBox99.Value
    Worksheets("Stockpile").Cells(I, 101) = Me.TextBox100.Value
    Worksheets("Stockpile").Cells(I, 102) = Me.TextBox101.Value
    Worksheets("Stockpile").Cells(I, 103) = Me.TextBox102.Value
    Worksheets("Stockpile").Cells(I, 104) = Me.TextBox103.Value
    Worksheets("Stockpile").Cells(I, 105) = Me.TextBox104.Value
    Worksheets("Stockpile").Cells(I, 106) = Me.TextBox105.Value
    Worksheets("Stockpile").Cells(I, 107) = Me.TextBox106.Value
    Worksheets("Stockpile").Cells(I, 108) = Me.TextBox107.Value
    Worksheets("Stockpile").Cells(I, 109) = Me.TextBox108.Value
    Worksheets("Stockpile").Cells(I, 110) = Me.TextBox109.Value
    Worksheets("Stockpile").Cells(I, 111) = Me.TextBox110.Value
    Worksheets("Stockpile").Cells(I, 112) = Me.TextBox111.Value
    Worksheets("Stockpile").Cells(I, 113) = Me.TextBox112.Value
    Worksheets("Stockpile").Cells(I, 114) = Me.TextBox113.Value
    Worksheets("Stockpile").Cells(I, 115) = Me.TextBox114.Value
    Worksheets("Stockpile").Cells(I, 116) = Me.TextBox115.Value
    Worksheets("Stockpile").Cells(I, 117) = Me.TextBox116.Value
    Worksheets("Stockpile").Cells(I, 118) = Me.TextBox117.Value


    Worksheets("Export").Cells(X, 5) = Me.TextBox118.Value
    Worksheets("Export").Cells(X, 6) = Me.TextBox119.Value
    Worksheets("Export").Cells(X, 7) = Me.TextBox120.Value
    Worksheets("Export").Cells(X, 8) = Me.TextBox121.Value
    Worksheets("Export").Cells(X, 9) = Me.TextBox122.Value
    Worksheets("Export").Cells(X, 10) = Me.TextBox123.Value
    Worksheets("Export").Cells(X, 11) = Me.TextBox124.Value
    Worksheets("Export").Cells(X, 12) = Me.TextBox125.Value
    Worksheets("Export").Cells(X, 13) = Me.TextBox126.Value
    Worksheets("Export").Cells(X, 14) = Me.TextBox127.Value
    Worksheets("Export").Cells(X, 15) = Me.TextBox128.Value
    Worksheets("Export").Cells(X, 16) = Me.TextBox129.Value
    Worksheets("Export").Cells(X, 17) = Me.TextBox130.Value
    Worksheets("Export").Cells(X, 18) = Me.TextBox131.Value
    Worksheets("Export").Cells(X, 19) = Me.TextBox132.Value
    Worksheets("Export").Cells(X, 20) = Me.TextBox133.Value
    Worksheets("Export").Cells(X, 21) = Me.TextBox134.Value
    Worksheets("Export").Cells(X, 22) = Me.TextBox134.Value
    Worksheets("Export").Cells(X, 23) = Me.TextBox136.Value
    Worksheets("Export").Cells(X, 24) = Me.TextBox137.Value
    Worksheets("Export").Cells(X, 25) = Me.TextBox138.Value
    Worksheets("Export").Cells(X, 26) = Me.TextBox139.Value
    Worksheets("Export").Cells(X, 27) = Me.TextBox140.Value
    Worksheets("Export").Cells(X, 28) = Me.TextBox141.Value
    Worksheets("Export").Cells(X, 29) = Me.TextBox142.Value
    Worksheets("Export").Cells(X, 30) = Me.TextBox143.Value
    Worksheets("Export").Cells(X, 31) = Me.TextBox144.Value
    Worksheets("Export").Cells(X, 32) = Me.TextBox145.Value
    Worksheets("Export").Cells(X, 33) = Me.TextBox146.Value
    Worksheets("Export").Cells(X, 34) = Me.TextBox147.Value
    Worksheets("Export").Cells(X, 35) = Me.TextBox148.Value
    Worksheets("Export").Cells(X, 36) = Me.TextBox149.Value
    Worksheets("Export").Cells(X, 37) = Me.TextBox150.Value
    Worksheets("Export").Cells(X, 38) = Me.TextBox151.Value
    Worksheets("Export").Cells(X, 39) = Me.TextBox152.Value
    Worksheets("Export").Cells(X, 40) = Me.TextBox153.Value

    UserForm1.Hide
    End Sub
    [/vba]
    Reply With Quote Reply With Quote
  2. 01-11-2012, 10:43 PM #2
    nilem
    nilem is offline
    VBAX Regular
    Joined
    Nov 2011
    Location
    Ufa
    Posts
    75
    Location
    try this
    [VBA]Private Sub UserForm_Activate()
    Dim i As Long
    For i = 1 To 153
    Me.Controls("TextBox" & i) = ""
    Next i
    Me.ComboBox1.Clear
    End Sub

    Private Sub CommandButton1_Click()
    Dim i As Long, j As Long

    With Worksheets("Stockpile")
    i = .Range("A375").End(xlUp).Row + 1
    .Cells(i, 1).Resize(, 4).Value = Array(dt, Me.TextBox2.Value, Me.TextBox3.Value, Me.ComboBox1.Value)
    For j = 4 To 117
    .Cells(i, j + 1) = Me.Controls("TextBox" & j)
    Next j
    End With

    With Worksheets("Export")
    i = .Range("A375").End(xlUp).Row + 1
    .Cells(i, 1).Resize(, 4).Value = Array(dt, Me.TextBox2.Value, Me.TextBox3.Value, Me.ComboBox1.Value)
    For j = 5 To 40
    Worksheets("Export").Cells(x, j) = Me.Controls("TextBox" & j + 113)
    Next j
    End With

    Me.Hide
    End Sub[/VBA]
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules