Problem with "INDIRECT()" within VBA EVALUATE function

[cgcamal]

Hi to all,

I'm using a great formula that returns the position of last number within a text string, the formula I get it from
http://www.mrexcel.com/forum/showthread.php?t=367606 and is as follow.

=MATCH(10^99,INDEX(--MID(A1,ROW(INDIRECT(""1:""&LEN(A1))),1),0))

When is entered in a cell is works correctly, but I receive error 2023 when I tried to use it within
EVALUATE() in VBA.

It looks the problem is when the part in red is evaluated.

Sub LastNum_In_String()
LN= Evaluate("MATCH(10^99,INDEX(--MID(A2,ROW([B][COLOR=Red]INDIRECT(""1:""&LEN(A2))[/COLOR][/B]),1),0))")
End Sub

Any ideas in how to solve this?

I'm not sure if the same result (get position of the last number) could be obtained without using "INDIRECT" function.

Many thanks in advance

Regards

 

[shg]

One way:

    Dim iLen As Long
    iLen = Len(Range("A2").Value)
    LN = Evaluate("=MATCH(2^15, --MID(A2, ROW(1:" & iLen & "), 1))")

 

[shg]

Or ...

    LN = Evaluate("=MATCH(2^15, --MID(A2, ROW(1:" & Len([a2]) & "), 1))")

 

[cgcamal]

Great shg,

It works correctly and is now smaller too, without using 2 functions.

Really thanks for your help and time.

Best regards

 

[shg]

You're welcome, glad it worked for you.