+ Reply to Thread
Results 1 to 3 of 3

Finding Item in Array then Executing Code

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    44

    Finding Item in Array then Executing Code

    Hi All,

    I am trying to find an item within an array and then VBA could execute code.

    Here is an example:

    Please Login or Register  to view this content.
    LookupItems =("text", "value", "book") or should I acutally be using Split("text,value,book",",")

    For Each sht in ThisWorkbook
    If sht.name = array(LookupItems) Then.....execute code
    Please Login or Register  to view this content.

    So basically in this example I want to loop through all the sheet names in the workbook and if any of the names in the arrary are found it will execute the code for those particular sheet names.

    I know alternatives are the select case or write an if statement for each value I am looking up or even use an OR for each value to lookup; but I just wanted to see if this method was even possible as it would be less coding. Thank for the suggestions in advance.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Finding Item in Array then Executing Code

    Hi,

    As well as looping through the sheets, you need an inner loop to loop through the values in the array so that you can compare each array value with the sheet name identified in the main For Each sht ..Loop
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Finding Item in Array then Executing Code

    another idea would be to use Match or Instr. Here is example using Match.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code to finding matching listbox item on sheet.
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2014, 03:18 PM
  2. Searching Item Code And Item Number
    By Shi in forum Excel General
    Replies: 3
    Last Post: 11-23-2013, 03:33 AM
  3. Need Help With Executing Built-in Menu Item Using VBA
    By PosseJohn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-20-2013, 06:30 PM
  4. Finding a specific word in a cell and executing an array
    By jgray152 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2010, 07:49 PM
  5. do anybody have a sample code for executing excel macro from vb code?<eom>
    By B Deepak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2005, 05:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1