Evaluate formula using a variable as range

[arlogenix]

I am trying to evaluate a formula with this code

Sub test()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:B10")
'answer = Application.WorksheetFunction.Max(myRange)
answer = [SUMPRODUCT((LEN(myRange)>0)*1)]
MsgBox answer
End Sub

But I getting an error because I am passing the variable MyRange. which is an important part of this formula, because the ranges would change on different conditions

Please can anyone help

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Try the Worksheet.Evaluate method - something like this

Sub test()
    Dim myRange As Range, answer As Long
    
    With Worksheets("Sheet1")
        Set myRange = .Range("A1:B10")
        'answer = Application.WorksheetFunction.Max(myRange)
        answer = .Evaluate("=SUMPRODUCT((LEN(" & myRange.Address & ")>0)*1)")
    End With
    MsgBox answer
End Sub

M.

 

[footoo]

You can't use square brackets if there is a variable within the Evaluate method.
Either like Marcelo Branco has posted or :answer = [SUMPRODUCT((LEN(A1:B10)>0)*1)]