How to convert Index(match.. (Left(Right...)..)..) into VBA (excel)
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:
Code:
=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 :
Code:
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!