Consulting

Results 1 to 5 of 5

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

  1. #1

    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:
    =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!
    Last edited by Alternsti; 01-22-2022 at 07:30 AM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Quote Originally Posted by Paul_Hossler View Post
    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!

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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)
    Last edited by p45cal; 01-22-2022 at 07:41 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Quote Originally Posted by p45cal View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •