none
Multiple Lookup RRS feed

  • Question

  • Hi,

    I've the following 2 tables:

        
    VARIABLES   TYPEA_A1  TYPEA_A2  TYPEA_A3
    VAR 1           
    VAR 2           
    VAR 3           
    VAR 4
    
    
                    
    VARIABLES   TYPEB_A1  TYPEB_A2  TYPEB_A3
    VAR 1                   
    VAR 3           
    VAR 4

     And I want to create at table in this form by combining these 2 tables:

    
    VARIABLES   TYPEA_A1  TYPEA_A2  TYPEA_A3  TYPEB_A1  TYPEB_A3
    VAR 1                   
    VAR 2                   
    VAR 3                   
    VAR 4       

    So, basically I've to use lookup by 2 conditions, by column as well as rows. And I want to do it using VBA. The following code I'm using:

    Sub LookUpProb()    
    Dim ColRng As Long    
    Dim RowRng As Long    
    Dim i As Long    
    Dim j As Long   
     ColRng = Worksheets("Sheet1").Cells(1, Worksheets("Sheet1").Columns.Count).End(xlToLeft).Column    RowRng=Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row        
    For i = 2 To ColRng            
    For j = 2 To RowRng    
    Worksheets("Final").Cells(i, j) = Application.WorksheetFunction.Index(Worksheets("Sheet1").Range("Worksheets(Sheet1)!B5:M22"), _    Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("Worksheets(Sheet1)!$A$5"), _    Worksheets("Sheet1").Range("Worksheets(Sheet1)!$A$5:$A$22"), 0), _    Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("Worksheets(Sheet1)!$B$4"), _    Worksheets("Sheet1").Range("Worksheets(Sheet1)!$B$5:$M$4")))        
    Next j            
    Next i
    End Sub

    But when I'm running this code I'm getting 1004 error. Can anybody please tell me where I'm making the mistake?

    Thank you!

    P.S. When I'm using the formula I'm getting the desired result. But when using VBA I'm having issues.





    Nemo


    • Edited by N_emo Monday, August 12, 2013 2:49 PM
    Monday, August 12, 2013 2:36 PM

All replies

  • I have no way to test the code, but my revised version looks like it might work.
    You had extra "Worksheets("Sheet1")" enclosed in quotemarks and there was no match type for the last Match function.
    I also added an object reference to Worksheets("Sheet1") so as to drastically shorten the length of the code and make it more readable...
    '---
    'For reference
    'MATCH(lookup_value, lookup_array, match_type)
    'INDEX(array, row_num, column_num)

    Sub LookUpProb()
    Dim ColRng As Long
    Dim RowRng As Long
    Dim i As Long
    Dim j As Long
    Dim WS As Excel.Worksheet

    Set WS = Worksheets(Sheet1)
    ColRng = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
    RowRng = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row

    For i = 2 To ColRng
      For j = 2 To RowRng
        With Application.WorksheetFunction
          Worksheets("Final").Cells(i, j) = .Index(WS.Range("B5:M22"), _
         .Match(WS.Range("$A$5"), WS.Range("$A$5:$A$22"), 0), _
         .Match(WS.Range("$B$4"), WS.Range("$B$5:$M$4"), 0))
        End With
      Next j
    Next i
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 8:32 PM
    Monday, August 12, 2013 5:43 PM