+ Reply to Thread
Results 1 to 8 of 8

Vba index and match for dynamic range

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    15

    Vba index and match for dynamic range

    I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".

    I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").
    I am struggling to do this with VBA...requesting the VBA guru's to help solve this.

    On Sheet2
    ID Data1 Data2 Data3 Data4 Data5 Data6 Data7
    1 INDEX(Sheet1!D4:M16,MATCH(Sheet2!C3,Sheet1!D4:D16,0),2)
    1 12
    1 0
    1 A
    A
    A

    SHEET 1 (Dynamically increasing range both for columns and rows from starting Column "D")
    ID Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Data9
    1 12 1 2
    1 12
    1
    1 A
    A
    A 12


    1

    A
    4

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Vba index and match for dynamic range

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-10-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vba index and match for dynamic range

    Dear Norie,

    Please find the attached file, hoping it will make clear at what I am looking at . I want to automise this with VBA as it will be part of VBA program.

    Thank for help
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-10-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vba index and match for dynamic range

    Do I need to give more info

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Vba index and match for dynamic range

    What exactly do you need help with?

    If it's to handle the increasing range you could do it without VBA by using a dynamic named range.

  6. #6
    Registered User
    Join Date
    02-10-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vba index and match for dynamic range

    Hi
    I have it out Vba using dynamic named range and index n match formula to look up array and get out put

    I want to do it using VBA....set up range and lookup and get values using index and match functions

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Vba index and match for dynamic range

    You don't need VBA though.

    The dynamic named ranges can be set up manually.

  8. #8
    Registered User
    Join Date
    02-10-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vba index and match for dynamic range

    hi guru
    I was looking for VBA solution only since it will be part of VB code
    If it cannot be done than v can close

+ Reply to Thread

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