Results 1 to 4 of 4

Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers

Threaded View

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Providence, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    6

    Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers

    I cannot seem to get this to work and i am hoping one of the resident experts can shed some light on what i am doing wrong.

    Essentially, I am trying to use a combination of Index and Match functions from within VBA to identify multiple criteria and then return a related value from another column in the identified row. I have attached a sample file with the following 2 simple scenarios:

    On a Userform are 2 scenarios:

    Single Criteria [WORKING] - Selecting a date from the top combobox triggers the macro to match the selection with a column on the "Raw Data" sheet. It if there is a match it puts the corresponding value from column C ("Flag") into the top label on the userform.

    Multiple Criteria [NOT WORKING] - Selecting a date from the bottom combobox triggers the macro to match the selection with a column on the "Raw Data" sheet. It also attempts to match a string (stock symbol) from the "Main" worksheet in A2 to a column on the "Raw Data" worksheet. The bottom label on the userform should only show a value if both criteria are met.

    After spending some time on this and learning about the behavior of the Match function in VBA i think i have gotten very close... but i am still missing something. Hopefully it is syntax related. Any help would be greatly appreciated!

    Single Criteria Code[Working]:
    Private Sub combobox1_Change()
        Dim TheDate As Date
        Dim TheSymbol
        Dim Match As Variant
        Dim colSymbol As Range
        Dim colDate As Range
        Dim colFlag As Range
        
        TheSymbol = Sheets("Main").Range("A2").Value
        TheDate = Menu2.combobox1.Value
        
        With Worksheets("Raw Data")
            Set colSymbol = .Range("A2:A" & .Range("A65536").End(xlUp).row)
            Set colDate = .Range("B2:B" & .Range("B65536").End(xlUp).row)
            Set colFlag = .Range("C2:C" & .Range("C65536").End(xlUp).row)
        End With
    
        On Error Resume Next
        'ONLY 1 CRITERIA & 1 CRITERIA RANGE
        Match = Application.Match(CLng(TheDate), colDate, 0)
        On Error GoTo 0
    
        If IsError(Match) Then
            msgbox "Not Found"
        Else
            Menu2.flag01.Caption = Format(Application.Index(colFlag, Match, 0), "X")
        End If
    End Sub
    Multiple Criteria Code [Not Working]:
    Private Sub ComboBox2_Change()
        Dim TheDate As Date
        Dim TheSymbol
        Dim Match As Variant
        Dim colSymbol As Range
        Dim colDate As Range
        Dim colFlag As Range
        
        TheSymbol = Sheets("Main").Range("A2").Value
        TheDate = Menu2.ComboBox2.Value
        
        With Worksheets("Raw Data")
            Set colSymbol = .Range("A2:A" & .Range("A65536").End(xlUp).row)
            Set colDate = .Range("B2:B" & .Range("B65536").End(xlUp).row)
            Set colFlag = .Range("C2:C" & .Range("C65536").End(xlUp).row)
        End With
    
        On Error Resume Next
        'MULTIPLE CRITERIA & MULTIPLE CRITERIA RANGES
        Match = Application.Match(CLng(TheDate) & CStr(TheSymbol), colDate & colSymbol, 0)
        On Error GoTo 0
    
        If IsError(Match) Then
            msgbox "Not Found"
        Else
            Menu2.flag01.Caption = Format(Application.Index(colFlag, Match, 0), "X")
        End If
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using MATCH and INDEX function with multiple criteria
    By Babylon in forum Excel Programming / VBA / Macros
    Replies: 52
    Last Post: 02-06-2014, 11:28 AM
  2. INDEX MATCH with multiple same dates
    By mcayea in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-08-2013, 10:58 AM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. Index and match function multiple criteria
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2013, 05:27 AM
  5. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM

Tags for this Thread

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