CODEWITHSUNDEEP
excelvba
Bob Vekemans
Bob Vekemans

Reputation: 1

The SUM function in VBA using NAMED RANGES only shows errors

I defined two named ranges; one (myplace) for the location to write the result to and one (myrange) for the range that needs to be summed. Compiler returns an error

Tried the formula method for sum, but this returns "#NAAM" error

This is the simple code I used:

Sub optelsom()

myplace = Range("plaats") 
myrange = Range("deelsom")

Range(myplace).Value = Application.WorksheetFunction.Sum(myrange)

End Sub

Upvotes: 0

Views: 25

Answers (1)

SJR
SJR

Reputation: 23081

(1) Get in the habit of declaring your variables (as per PEH's suggestion, use Option Explicit: in the VBA editor go to Tools › Options › Require Variable Declaration)

(2) You need Set when assigning object variables such as ranges

(3) myplace is already a range so no need for a 'wrapper'

Sub optelsom()

Dim myplace as range, myrange as range

Set myplace = Range("plaats") 
Set myrange = Range("deelsom")

myplace.Value = Application.WorksheetFunction.Sum(myrange)

End Sub

Upvotes: 1

Related Questions