The result of Evaluate (on a range) is incorrect-CodePudding

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

CodePudding user response：

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 & ")")

CodePudding user response：

Alternatively you can use the formula itself:

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

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