Compile Error: Expected End Sub

[jzkemler1]

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

 

[shg]

Welcome to the board.

Remove the lines that appear above Private Sub ...

 

[jasonb75]

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

You just need

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 -->

 

[Marcelo Branco]

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.

 

[jzkemler1]

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

 

[shg]

You can do that like this ...

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 ...

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.

 

[Marcelo Branco]

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

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.

 

[jzkemler1]

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

 

[shg]

I also tried the second option but it added the value of g34 to cell O67

My bad:

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).

 

[jzkemler1]

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