How to convert Index(match.. (Left(Right...)..)..) into VBA (excel)

[Alternsti]

Hi,


I'm trying to replace the working formule below into VBA code but the macro is always stopping (highlighted in yellow intio VBA developer) at the VBA line (see below).


Cell formula:

=INDEX(Latest_Range;MATCH(LEFT(RIGHT(Z2;11);5);LatestLineNo;0);11)

array: Latest_Range (having more than 100 columns x 4000 lines), LatestLineNo (5 digits, string only)
Z2: (50 digits, string only) which have the 5 digits string I must check


VBA :

Sheets("Existing 2W").Range("S2").Value = WorksheetFunction.Index(Range("Latest_Range"), WorksheetFunction.Match(Left(Right((Sheets("Existing 2W").Range("S2")), 11), 5).Value, Range("LatestLineNo"), 0), 11)

Can you explain what I did wrong?


Thanks!