none
VBA Index/Match equivalent help RRS feed

  • Question

  • Hi everyone,

    I have a Data sheet with approx. 50,000 rows of data across 12 columns that I'm trying to apply an index/match equivalent of VBA on.  This is how the workbook is set up:

    Data sheet

    Column Y: contains values I'm trying to match to an inputted value

    Column A: has the index values that correspond to the inputted value matched in column Y

    Report sheet

    Cell D7: the user will input a numerical value in this cell, which will be matched to column Y in the Data sheet

    Cells A10 - down: the resulting index values from Column A in the Data sheet will be displayed here starting from cell A10 in the Report sheet and down until they are all displayed.

    What I'm trying to do

    I have a sample workbook but I can't seem to find anywhere to upload it.  Anyways, what I'm trying to do:

    1. the user enters a numerical value in D7 of the report worksheet

    2. The user will activate the macro which will match the results of their entry to the Data sheet column Y, and list the corresponding match values from column A of the Data sheet in column A of the Report sheet, starting from cell A10 and listing downward until all the values have been displayed.

    Any ideas?  Thanks very much for everyone's help!

    Wednesday, February 10, 2016 3:02 PM

All replies

  • Filters are the way to go: this macro could be started by the user's entry of a value in cell D7 - an option that may be too responsive.

    Sub TestMacro()
    '    Optional clearing of Report Sheet
        With Sheets("Report Sheet")
            .Range("A10:A" & .Rows.Count).ClearContents
        End With
        With Sheets("Data Sheet")
            .Range("Y:Y").AutoFilter Field:=1, Criteria1:=Sheets("Report Sheet").Range("D7").Value
            Intersect(.UsedRange, .Range("A:A")).Copy Sheets("Report Sheet").Range("A10")
            .Range("Y:Y").AutoFilter
        End With
    End Sub

    If you want to upload a workbook, you need to have an account at an online file storage service, like onedrive.live.com and then post a link to your file in your message.



    Wednesday, February 10, 2016 5:41 PM