VBA Format for a Range Name Used in the Sum Function

[DogLover]

I am trying to run some VBA code that I would use Range names rather than a
specific range, ie Data!M3:M223.

MsgBox Evaluate("SUM( Data!M3:M223 )") will work, but when I change it to
include the Range name representing Data!M3:M223, it does not. Can anyone
help to figure out what I'm doing wrong. Thanks in advance.


Dim mTimeCriteria As String
Dim mPosition As String
Dim mFormula As String
Dim mQuestion1Range As Range


mTimeCriteria = "First day of employment (Time 1)"
mPosition = "Registered Nurse"
mPositionRange = Worksheets("Data").Range("DataPosition")
Set mTimeRange = Worksheets("Data").Range("DataTime")
Set mQuestion1Range = Worksheets("Data").Range("DataQuestion1")


MsgBox Evaluate("SUM( mQuestion1Range )")

 

[Dan]

MsgBox Evaluate(Application.WorksheetFunction.Sum(mQuestion1Range))

Dan

 

[DogLover]

Thanks Dan. It works now!

MsgBox Evaluate(Application.WorksheetFunction.Sum(mQuestion1Range))

Dan

 

[DogLover]

Expanding functionality now. I am going to want to use multiple criteria and
am just testing how to use the SumProduct in VBA before I expand to multiple
Criteria.
I seem to have my format off and wondered if anyone can see what is wrong.

MsgBox Evaluate(Application.WorksheetFunction.SumProduct((mTimeRange =
"First day of employment (Time 1)") * (mQuestion1Range)))