Results 1 to 7 of 7

Thread: Looping VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looping VBA

    I am trying to move data from a user form to an excel sheet and the code below does what I need for the first instance. The form may contain up to 100 text boxes txtUnits1, txtUnits2, txtUnits3 etc

    What I need to do is for every text box move the corresponding data. If there isn't any values in the text box nothing gets moved.

    I could copy the code below and change values but have been reading about loops but as I'm new to this I don't know where to start.

    Can anyone help point me in the right direction to move the same data for txtUnits2 etc

    Dim ws As Worksheet
    Dim Addto As Range
    Set ws = Sheet2
    Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)


    With ws


    If txtUnits1.Value > 0 Then Addto = txtName.Value
    If txtUnits1.Value > 0 Then Addto.Offset(0, 1).Value = cboStore.Value
    If txtUnits1.Value > 0 Then Addto.Offset(0, 2).Value = txtProduct.Value
    If txtUnits1.Value > 0 Then Addto.Offset(0, 4).Value = txtUnits1.Value

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,466
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Looping VBA

    Hi billBob,

    please when you post code, paste it between code brackets, see example below in red.

    Your textboxes are controls. And controls are held in a collection. And collections allow you to loop through their contents.
    This is the case for cells in a range, for sheets in a workbook, for shapes in a sheet, etc.

    The way to do that is to declare a variable as the object you want to loop through, and than use the 'For Each [object] in [Collection] ... Next' loop.

    In your case something like:
    Code:
        Dim wsS2 As Worksheet
        Dim rAddto As Range
        Dim ctTB As MSForms.Control
        
        Set wsS2 = Sheet2
        
        
        With wsS2
            Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
            
            'Loop through all the controls
            For Each ctTB In Me.Controls    ' Me is the UserForm itself
                If Left(ctTB.Name, 8) = "txtUnits" Then
                Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                             Press Ctrl-G  to see this immediate window, bottom left below _
                                             the edit window
                    
                If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                    rAddto = txtName.Value
                    rAddto.Offset(0, 1).Value = cboStore.Value
                    rAddto.Offset(0, 2).Value = txtProduct.Value
                    rAddto.Offset(0, 4).Value = txtUnits1.Value
                End If
            Next ctTB
    Note that I have renamed your variables slightly to indicate the type of variable. i also always use at least one capital letter in a variable when declaring it (Dim wsS2 as Sheet), because, when typing the code, (lower case only , wss2) VBA will then capitalise the letters (wss2 -> wsS2). If my variable in the code thne does not capitalise, it means I have made a typo.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    New Member
    Join Date
    Jan 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping VBA

    Hi sijpie

    Thanks for your help. Thanks for the advice on how to post.

    I have tried your code and from how you've explained I can work through how this works. I have run and got a compile error Next without For

    Do you have any ideas on why I'm getting this error?

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping VBA

    Code:
        With wsS2
            Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
            
            'Loop through all the controls
            For Each ctTB In Me.Controls    ' Me is the UserForm itself
                If Left(ctTB.Name, 8) = "txtUnits" Then
                Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                             Press Ctrl-G  to see this immediate window, bottom left below _
                                             the edit window
                ' My guess, is you want an END IF right here...    
                If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                    rAddto = txtName.Value
                    rAddto.Offset(0, 1).Value = cboStore.Value
                    rAddto.Offset(0, 2).Value = txtProduct.Value
                    rAddto.Offset(0, 4).Value = txtUnits1.Value
                End If
            Next ctTB
    Quote Originally Posted by BILLYBOB1972 View Post
    Hi sijpie

    Thanks for your help. Thanks for the advice on how to post.

    I have tried your code and from how you've explained I can work through how this works. I have run and got a compile error Next without For

    Do you have any ideas on why I'm getting this error?

    You have two 'If' statements...but only one 'End If.

    This is causing your loop to fail. Close the other If statement and your loop will be fine. I would imagine the missing End If goes right below the Debug.Print
    Last edited by Steve_; Jul 22nd, 2019 at 06:40 PM.

  5. #5
    New Member
    Join Date
    Jan 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping VBA

    Hi Steve

    im now getting a compile error Expected End With

    Any ideas

  6. #6
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping VBA

    Add an

    End With

    after

    Next ctTB

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,466
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Looping VBA

    Sorry, missed an End If
    Code:
       Dim wsS2 As Worksheet
        Dim rAddto As Range
        Dim ctTB As MSForms.Control
        
        Set wsS2 = Sheet2
        
        
        With wsS2
            Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
            
            'Loop through all the controls
            For Each ctTB In Me.Controls    ' Me is the UserForm itself
                If Left(ctTB.Name, 8) = "txtUnits" Then
                    Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                             Press Ctrl-G  to see this immediate window, bottom left below _
                                             the edit window
                    
                    If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                        rAddto = txtName.Value
                        rAddto.Offset(0, 1).Value = cboStore.Value
                        rAddto.Offset(0, 2).Value = txtProduct.Value
                        rAddto.Offset(0, 4).Value = txtUnits1.Value
                    End If
                End If
            Next ctTB
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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