Results 1 to 6 of 6

Thread: How to loop the VBA Code

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post How to loop the VBA Code

    Hello There, I have the following Code and I want to loop through the following Code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$12" Then
            Range("C12").Value = "Please Select..."
        End If
        If Target.Address = "$C$12" Then
            Range("D12").Value = "Please Select..."
            Range("E12").Value = "Please Select..."
            Range("F12").Value = "Please Select..."
            Range("G12").Value = "Please Select..."
            Range("H12").Value = "Please Select..."
        End If
        
        If Target.Address = "$B$13" Then
            Range("C13").Value = "Please Select..."
        End If
        If Target.Address = "$C$13" Then
            Range("D13").Value = "Please Select..."
            Range("E13").Value = "Please Select..."
            Range("F13").Value = "Please Select..."
            Range("G13").Value = "Please Select..."
            Range("H13").Value = "Please Select..."
        End If
    
    End Sub
    The idea of the code that if data validation list value changed the other cells whom are related to that list will replace what is inside it to "Please Select..." to start new selection so instead of copy and paste the code for 20 rows I need to loop it.

    Regards.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: How to loop the VBA Code

    I don’t quite understand what you want.
    If B12 is changed then C12:H12 will have "Please Select..."
    If C12 is changed then D12:H12 will have "Please Select..."
    & you want the same thing for row 12-31 (20 rows)
    Is that correct?

    Try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
            
            If Target.Cells.Count = 1 Then
            Application.EnableEvents = False
                If Target.Column = 2 Then
                Target.Offset(, 1) = "Please Select..."
                ElseIf Target.Column = 3 Then
                Target.Offset(, 1).Resize(, 5) = "Please Select..."
                End If
            
            Application.EnableEvents = True
            End If
           
        End If
    
    End Sub
    Last edited by Akuini; Jul 31st, 2019 at 09:34 AM. Reason: edit code

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to loop the VBA Code

    You can select a bigger target (in my example rows 12-32) and apply it to the row instead of looping:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      'Change in B column
        If Not Intersect(Target, Range("B12:B32")) Is Nothing Then
            Range("C" & Target.Row).Value = "Please Select..."
        End If
      'Change in C column
        If Not Intersect(Target, Range("C12:C32")) Is Nothing Then
             Range("D" & Target.Row).Value = "Please Select..."
             Range("E" & Target.Row).Value = "Please Select..."
             Range("F" & Target.Row).Value = "Please Select..."
             Range("G" & Target.Row).Value = "Please Select..."
             Range("H" & Target.Row).Value = "Please Select..."
        End If
    End Sub
    In this case, change in column B modify value in C which trigger the second part of the event. You can escape using 'GoTo' or 'End Sub'.
    Last edited by Kamolga; Jul 31st, 2019 at 09:41 AM.

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to loop the VBA Code

    Quote Originally Posted by Akuini View Post
    I donít quite understand what you want.
    If B12 is changed then C12:H12 will have "Please Select..."
    If C12 is changed then D12:H12 will have "Please Select..."
    & you want the same thing for row 12-31 (20 rows)
    Is that correct?

    Try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
            
            If Target.Cells.Count = 1 Then
            Application.EnableEvents = False
                If Target.Column = 2 Then
                Target.Offset(, 1) = "Please Select..."
                ElseIf Target.Column = 3 Then
                Target.Offset(, 1).Resize(, 5) = "Please Select..."
                End If
            
            Application.EnableEvents = True
            End If
           
        End If
    
    End Sub
    thank you so much this is exactly what I am looking for.

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: How to loop the VBA Code

    You're welcome, glad to help, & thanks for the feedback.

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: How to loop the VBA Code

    Sorrry, the above code will change only C12 if B12 changes. If you want it to change C12:H12 then use this one instead:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("B12:C31")) Is Nothing Then
            
            If Target.Cells.Count = 1 Then
            Application.EnableEvents = False
                If Target.Column = 2 Then
                Target.Offset(, 1).Resize(, 6) = "Please Select..."
                ElseIf Target.Column = 3 Then
                Target.Offset(, 1).Resize(, 5) = "Please Select..."
                End If
            
            Application.EnableEvents = True
            End If
           
        End If
    
    End Sub

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
  •