CODEWITHSUNDEEP
excelvba
Peace
Peace

Reputation: 668

The result of Evaluate (on a range) is incorrect

I am using this line of code and it works correctly Range("S2:S7").Formula = "=LEN(E2)"
But, when I changed it to Evaluate , I got incorrect result of Len function (always = 9)
In advance thanks for your help. 

Sub Evaluate_Test()
 
    Dim rng As Range
    Set rng = Range("S2:S7")
    rng.value = Evaluate(Len(rng.Offset(0, -14).Address))
 
End Sub

Upvotes: 1

Views: 141

Answers (4)

Tim Williams
Tim Williams

Reputation: 166655

This works for me:

Sub Evaluate_Test()
    Dim rng As Range
    Set rng = ActiveSheet.Range("D2:D7")
    rng.Value = rng.Parent.Evaluate("=LEN(" & rng.Offset(0, -2).Address & ")")
End Sub

Input/result:

enter image description here

Upvotes: 0

Peace
Peace

Reputation: 668

This working answer is fully credits to @Rory :
To force the return of an array, I had to use Index with Len as the following code:

Sub Evaluate_Test()
    Dim rng As Range
    Set rng = Range("S2:S7")
    rng.value = Evaluate("INDEX(LEN(" & rng.Offset(0, -14).Address & "),0)")      'This Works correctly
End Sub

Upvotes: 0

Ike
Ike

Reputation: 13054

Alternatively you can use the formula itself:

rng.Formula = "=LEN(E2)"
rng.Value = rng.Value

or

rng.Formula = ="=LEN(" & rng.Offset(0, -14).Address & ")"

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

Your current set-up is passing a string (in this case the address $E$2:$E$7) to Len.

Try instead

Evaluate("LEN(" & rng.Offset(0, -14).Address & ")")

Upvotes: 2

Related Questions