Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Compile Error: Expected End Sub
Thanks Thanks: 0 Likes Likes: 0

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

    Unhappy Compile Error: Expected End Sub

    Hi!!
    Prefaced with I am a novice!!! But I am trying to get the macro below to work in Excel and I keep getting the Compile Error: Expected End Sub error. Can anyone with a trained set of eyes help me out? Thanks so much!! Zoe

    Sub ctrln()
    '
    ' ctrln Macro
    '
    ' Keyboard Shortcut: Ctrl+n
    '
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("G34")) Is Nothing Then
    With Target
    If IsNumeric(.Value) And Not IsEmpty(.Value) Then
    Range("I34").Value = Range("I34").Value + Range ("G34").Value
    End If
    End With
    End If
    End Sub

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,610
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Compile Error: Expected End Sub

    Welcome to the board.

    Remove the lines that appear above Private Sub ...

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,638
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub

    You have part of what looks like a recorded macro followed by an event procedure.

    You just need

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("G34")) Is Nothing Then
                With Target
                    If IsNumeric(.Value) And Not IsEmpty(.Value) Then
                        Range("I34").Value = Range("I34").Value + Range("G34").Value
                    End If
                End With
            End If
    End Sub
    not the part above it.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,266
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Compile Error: Expected End Sub

    Hi,

    As shg said you must remove the lines above Private Sub..., but you need also put this code on a worksheet page-code, not in a Standard Module.

    This is an event procedure, ie, it runs when something happens - in your case when G34 changes.

    For an excellent explanation take a look at
    http://www.cpearson.com/excel/Events.aspx

    M.

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

    Default Re: Compile Error: Expected End Sub

    Thank you!!

    I think perhaps I am going about this the wrong way entirely. I do want a macro that can be run when a user designates and not an event running everytime g34 is changed.

    I am hoping to create a macro that will add g34 to i34- displaying the sum in cell i34, then revert the contents of g34 back to 0 while keeping the addition of the original value in the i34 cell.

    So for example g34 would contain 100 and i34 would contain 660, the macro would be run and g34 would then contain 0 and i34 would contain 760. Then 14 would be entered into g34 and the macro run again and g34 would then contain 0 and i34 would contain 774.

    Is that even possible using a macro?

    again THANK YOU so much for taking a look

    Zoe

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,610
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Compile Error: Expected End Sub

    You can do that like this ...
    Code:
    Sub ctrln()
        If VarType(Range("G34").Value2) = vbDouble Then
            Range("I34").Value = Range("I34").Value + Range("G34").Value
        End If
    End Sub
    or like this ...
    Code:
    Sub ctrln()
        With Range("G34")
            If VarType(.Value2) = vbDouble Then
                .Copy
                .Range("I34").PasteSpecial Paste:=xlPasteValues, _
                                           Operation:=xlPasteSpecialOperationAdd
                Application.CutCopyMode = False
            End If
        End With
    End Sub
    I'd favor the second approach, and start with a formula in I34 (like =0) so you have an audit trail.

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,266
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Compile Error: Expected End Sub

    Or, if you wish, you can use an event procedure like

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("G34")) Is Nothing Then
            Application.EnableEvents = False
            With Target
                If IsNumeric(.Value) Then
                    Range("I34").Value = Range("I34").Value + .Value
                    .Value = 0
                End If
            End With
            Application.EnableEvents = True
        End If
    End Sub
    M.

  8. #8
    New Member
    Join Date
    Jan 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub

    Thank you shg,
    I tried the first option and it did add g34 to i34, but it did not remove the value from g34, is there a way to change g34 to '0' or clear it after its value is added to i34? Also I could not get the keyboard shortcut ctrl+n to work, where does the code go to do this? Can I add the following to the beginning of your option 1 to get it to work?

    Sub ctrln()
    '
    ' ctrln Macro
    '
    ' Keyboard Shortcut: Ctrl+n
    '

    I also tried the second option but it added the value of g34 to cell O67 ??? Weird right!!?

    You are awesome!!

    Zoe

  9. #9
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,610
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Compile Error: Expected End Sub

    I also tried the second option but it added the value of g34 to cell O67
    My bad:
    Code:
    Sub ctrln()
        With Range("G34")
            If VarType(.Value2) = vbDouble Then
                .Copy
                Range("I34").PasteSpecial Paste:=xlPasteValues, _
                                          Operation:=xlPasteSpecialOperationAdd
                Application.CutCopyMode = False
                .ClearContents
            End If
        End With
    End Sub
    Also I could not get the keyboard shortcut ctrl+n to work
    No code. Do Alt+F8, sleect ctrln from the list, press Options, and add the shortcut.

    The code should be in a standard code module (not a sheet module).

  10. #10
    New Member
    Join Date
    Jan 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub

    I cannot thank you enough, that works perfectly!! Thank you for taking your time to help me. Let me know where I can mail you cookies

    Zoe

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
  •