none
Loop counter problems (Help!) RRS feed

  • Question

  • Hi,

    I'm trying to program a macro which can be used to enter the different ingredients of a mixture (eg a softdrink) in a excel-worksheet.

    I'm using 2 userforms. The first enters the name of the drink and sets the number of ingredients. The second userform can be used to enter the ingredients. I want this userform to repeat until all ingredients are entered (ie number of loops is equal to the value entered in the first userform).

    Due to a complete lack of experience with programming it kind of lacks structure so far. The number of repeats needed is set in:

    UserForm2.textbox_number

    The code so far:

    Private Sub Cmdbutton_save_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'loop
    Dim sum, n As Integer
    Private Sub Form_Activate()
    List1.AddItem "n" & vbTab & "sum"
    Do
       n = n + 1
       sum = sum + n
     List1.AddItem n & vbTab & sum
     If n = UserForm2.textbox_number Then
     Exit Do
     End If
     Loop
    End Sub
    Dim sum, n As Integer
    
    'find first empty row in database
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    'check for a Substance
    If Trim(Me.TextBox6.value) = "" Then
    Me.TextBox6.SetFocus
    MsgBox "Please complete the form"
    Exit Sub
    End If
    
    'copy the data to the database
    ws.Cells(iRow, 3).value = Me.TextBox6.value
    ws.Cells(iRow, 4).value = Me.TextBox5.value
    ws.Cells(iRow, 5).value = Me.TextBox4.value
    ws.Cells(iRow, 6).value = Me.textbox_kow.value
    ws.Cells(iRow, 7).value = Me.textbox_pvp.value
    
    'clear the data
    Me.TextBox6.value = ""
    Me.TextBox5.value = ""
    Me.TextBox4.value = ""
    Me.textbox_kow.value = ""
    Me.textbox_pvp.value = ""

    All help is highly appreciated!


    Wednesday, May 29, 2013 10:33 AM