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!

[Paul_Hossler]

1. Define 'always stopping' -- is there an error message or something?

2. GUESSING - since it seems you use semicolons (probably based on the Excel language) in the WS formula, do you also need to use them in the VBA statement? I don't know

3. Can you attach a small sample workbook with the named ranges, some data, and the desired result?

[Alternsti]

1. Define 'always stopping' -- is there an error message or something?
the macro stop at this line and was hightlighted in yellow into Developer tab.

2. GUESSING - since it seems you use semicolons (probably based on the Excel language) in the WS formula, do you also need to use them in the VBA statement? I don't know
Latest_Range, LatestLineNo are both array name (I assume that's the way to put those into VBA code

3. Can you attach a small sample workbook with the named ranges, some data, and the desired result?
I've made some change into my post to show what is used as range names.

Thanks for your help!

[p45cal]

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

(the .Value needed to be moved too).

Using WorksheetFunction.Match will stop the code running if no match is found, likewise with WorksheetFunction.Index.
Using Application.Match and Application.Index instead will be more forgiving:

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

[Alternsti]

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

(the .Value needed to be moved too).

Using WorksheetFunction.Match will stop the code running if no match is found, likewise with WorksheetFunction.Index.
Using Application.Match and Application.Index instead will be more forgiving:

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

Both of your solutions are working fine. I'll stay with Application.Match and Application.Index.

Thank you very much for the help.