Results 1 to 3 of 3

Simple VBA Question - Search List of Strings Straight from VBA?

Threaded View

  1. #1
    Registered User
    Join Date
    04-11-2011
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2003
    Posts
    24

    Simple VBA Question - Search List of Strings Straight from VBA?

    Basically, I'm matching 4 strings currently, and have a bunch of extra code that I am trying to clean up. These are just defined by me within the code, not pulled from any Excel Worksheet.

    Is there a way to create the list in VBA and then just do a loop through the list?

    So I have something similar to this:

       Dim lMatch As Long
            lMatch = Application.Match("AString", ws.Range("A1:A20"), 0)
            If Not IsError(lMatch) Then
                *some code*
            End If
            lMatch = Application.Match("BString", ws.Range("A1:A20"), 0)
            If Not IsError(lMatch) Then
                *some code*
            End If
            lMatch = Application.Match("CString", ws.Range("A1:A20"), 0)
            If Not IsError(lMatch) Then
                *some code*
            End If
            lMatch = Application.Match("DString", ws.Range("A1:A20"), 0)
            If Not IsError(lMatch) Then
                *some code*
            End If
    Which you can see it's very redundant.

    I wanted to just make a loop where I store my search terms in some type of array in VBA and then loop through it, but I'm not sure on the exact way to do this.

    So something like:

    Dim iRow as Integer
    ???("AString", BString", CString", DString")
    For iRow = 1 to 4
        lMatch = Application.Match(???(iRow), ws.Range("A1:A20"), 0))
        If Not IsError(lMatch) Then 
             *some code*
        End If
    Next iRow
    Can I just return the position of the string to search from some type of array? I'm not really familiar with using this type of stuff, thanks.
    Last edited by Strife; 10-27-2011 at 06:22 PM.

Thread Information

Users Browsing this Thread

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

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