A SUM function in VBA with a Range parameter

Question

I am trying to write a macro in VBA for excel that allows a user to input a range of cells. Then, I want the macro to sum up all of the cells, and input them into cell R32.

I have attached the code that I wrote for it so far. When I run the macro, I am allowed to select my range of cells.

However, I then receive the error message, run time error 1004- method range of object _Worksheet failed. This seems like something that could be a quick fix... But I am very stuck. Any help would be greatly appreciated.

Private Sub CommandButton5_Click()
    Dim userResponse As Range
    On Error Resume Next

    Set userResponse = Application.InputBox("select a range with the mouse",Default:=Selection.Address, Type:=8)
    On Error GoTo 0

    If userResponse Is Nothing Then
        MsgBox "Cancel clicked"
    Else
        MsgBox "You selected " & userResponse.Address
    End If

    Range("R32").Value = Application.WorksheetFunction.Sum(Range(userResponse))
End Sub

test comment, just to make sure this post didnt get deleted.. — Max H
– Max H, Commented Jun 24, 2015 at 15:25
Range("R32").Value = Application.WorksheetFunction.Sum(Range(userResponse)) will give you an error when userResponse is nothing. You need to put it inside the if statement testing for nothingness. — D_Bester
– D_Bester, Commented Jun 24, 2015 at 15:32

Martin · Accepted Answer · 2015-06-24 15:27:56Z

4

The problem here is that you are using Range(userResponse) for your Sum function.

The variable userResponse is already a Range, you don't need to specify it as one again.

Try this line of code and you will find it works as expected:

Range("R32").Value = Application.WorksheetFunction.Sum(userResponse)

answered Jun 24, 2015 at 15:27

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arin Over a year ago

I upvoted this because I believe it's better than my answer. :-)

Max H Over a year ago

Thanks, this works as well! Gave it the green checkmark and upvote

Arin · Accepted Answer · 2015-06-24 15:26:44Z

2

What if you added .Address inside the Range() at the bottom, like so:

Range("R32").Value = Application.WorksheetFunction.Sum(Range(userResponse.Address))

Would that do what you're looking for?

answered Jun 24, 2015 at 15:26

Arin

1,3932 gold badges10 silver badges24 bronze badges

2 Comments

Max H Over a year ago

Thought I tried that already, but just put it in and it works! Thank you, will give you the check mark once it lets me (5 min delay...)

Arin Over a year ago

@MaxH, I'd have you try Martin Parkin's solution instead, as mine contains redundant code (getting the address from the range and then the range from that address). His is cleaner!

Collectives™ on Stack Overflow

A SUM function in VBA with a Range parameter

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related