Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Unanswered: End If Without Block If ERROR

    I am trying to use a code to move lines from one sheet to another based on input in column 1. Here is the code I am using.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range

    Set rng1 = Target.EntireRow
    Set rng2 = Worksheets("DQ").Cells(Rows.Count, 1).End(xlUp) _
    .Offset(1, 0)
    Set rng3 = Worksheets("VERBAL").Cells(Rows.Count, 1).End(xlUp) _
    .Offset(1, 0)
    If Target.Column = 1 Then
    On Error GoTo endit
    Application.EnableEvents = False
    If Target.Value = "DQ" Then
    With rng1
    .Copy Destination:=rng2
    .Delete
    If Target.Value = "V" Then
    With rng1
    .Copy Destination:=rng3
    .Delete
    End With
    End If
    End If
    endit: Application.EnableEvents = True
    End Sub

    I want to add another value if I can get this to work as well. Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    you seem to be missing and 'End IF'

    I think your code should be like this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng1 As Range
        Dim rng2 As Range
        Dim rng3 As Range
        
        
        Set rng1 = Target.EntireRow
        Set rng2 = Worksheets("DQ").Cells(Rows.Count, 1).End(xlUp) _
        .Offset(1, 0)
        Set rng3 = Worksheets("VERBAL").Cells(Rows.Count, 1).End(xlUp) _
        .Offset(1, 0)
        If Target.Column = 1 Then
            On Error GoTo endit
            Application.EnableEvents = False
            If Target.Value = "DQ" Then
                With rng1
                    .Copy Destination:=rng2
                    .Delete
                End With
                If Target.Value = "V" Then
                    With rng1
                        .Copy Destination:=rng3
                        .Delete
                    End With
                End If
            End If
        End If
    endit:
        Application.EnableEvents = True
    End Sub
    Indenting the code helps enormously.

    with a little rearranging it could be simplified a little like this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng1 As Range
        Dim rng2 As Range
        Dim rng3 As Range
    
        Set rng1 = Target.EntireRow
        Set rng2 = Worksheets("DQ").Cells(Rows.Count, 1).End(xlUp) _
        .Offset(1, 0)
        Set rng3 = Worksheets("VERBAL").Cells(Rows.Count, 1).End(xlUp) _
        .Offset(1, 0)
        
        If Target.Column = 1 Then
            On Error GoTo endit
            Application.EnableEvents = False
            With rng1
                If Target.Value = "DQ" Then
                    .Copy Destination:=rng2
                    .Delete
                ElseIf Target.Value = "V" Then
                    .Copy Destination:=rng3
                    .Delete
                End If
            End With
        End If
    endit:
        Application.EnableEvents = True
    End Sub
    ??
    HTH

    MTB

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    code indentation is ALWAYS a smart call, not just to make it easier for others to understand what your code does, but also it helps in situations like this. it also makes the code far easier for you, yourself to understand and read.

    if you are posting code on the forum remember to use the [ c o d e ] & [ / c o d e ], but without the spaces (eg [/code].

    if you use the 'advanced' post screen yoiu can use the # button to wrap your code appropriately

    to indent your code either do it manually or use a tool such as
    https://www.add-ins.com/macro-produc...t-vba-code.htm
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2014
    Posts
    2

    Thanks!

    That absolutely helped and allowed me to build on it going forward. Appreciate the help.

Tags for this 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
  •