CODEWITHSUNDEEP
excelvba
Max H
Max H

Reputation: 61

A SUM function in VBA with a Range parameter

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

Upvotes: 0

Views: 1303

Answers (2)

Martin
Martin

Reputation: 16433

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)

Upvotes: 4

Arin
Arin

Reputation: 1393

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?

Upvotes: 2

Related Questions