Compile Error: Expected End Sub

jzkemler1

New Member
Joined
Jan 4, 2012
Messages
13
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the board.

Remove the lines that appear above Private Sub ...
 
Upvote 0
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.
<!-- / message -->
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top