Excel VBA: Using Index/Match down a column

[KC E]

Yes, using Excel 2013, this is a continuation of a thread I posted earlier to try to speed up code I'm using on 2 sheets of data: https://chandoo.org/forum/threads/e...o-match-criteria-btw-wkbks.36033/#post-216132.

I have since moved the data to 2 worksheets within the same workbook (attached) to help speed things up b/c the data on the Billing wksht can be up to 300,000 rows--the Address List is the 1st wksht, and the Billing sheet is the 2nd wksht.

1) I'm using the 'Helper Address' column H, starting with cell H2, in the Address wksht as the lookup value to lookup values 2) in the 'Helper Address' column E (the lookup range), starting in E2, in the Billing wksht.

Where the answers will go:
The code will match the addresses btw. the Billing wkbk and Address wkbk and return that address (column E) from the Billing wkbk to the 'Address' column of the Address wkbk in column D.

Narayan stated in the thread I created in the link above that I could use Index/Match instead of looping through cells.

I've adapted code below to do that and added used With/End With, etc. to speed things up, but when the Index/Match puts the formula down column D of the Address List it just says #Name? down the cells. I have at least 2 addresses that should match between the wkshts.

None of the variables are capitalized in the Index/Match part of the code, though I don't get any errors when I run the code.

Can anyone help with what I am doing wrong?

Thank you.

Sub Test()

    Dim MatchTable, IndexTable As Range
    Dim LRow, ALrow, MatchAnsw As Long 'ALrow is address list last row
    Dim ToFindMatch As Variant
 
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

 
    With ActiveWorkbook

        With .Sheets(2) 'billing
            LRow = .Cells(Rows.Count, 5).End(xlUp).Row
            Set MatchTable = .Range("$e$2:$E" & LRow)
            Set IndexTable = .Range("$E$2:$E" & LRow)
        End With

        With .Sheets(1) 'address list
            ALrow = .Cells(Rows.Count, 8).End(xlUp).Row
            ToFindMatch = .Range("$H2").Value
       End With

        With .Sheets(1).Range("D2:D" & ALrow) 'address list
            .Formula = "=index(indextable, match(tofindmatch,matchtable,0))"
        End With
         
    End With
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

source: https://www.mrexcel.com/forum/excel-questions/728725-vba-formula-index-match-dynamic-range.html

 

[Hui]

Try this modification:

Sub Test()

  Dim MatchTable, IndexTable As Range
  Dim LRow, ALrow, MatchAnsw As Long 'ALrow is address list last row
  Dim ToFindMatch As Variant
  Dim cell As Range
   
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  With ActiveWorkbook

  With .Sheets(2) 'billing
  LRow = .Cells(Rows.Count, 5).End(xlUp).Row
  Set MatchTable = .Range("$e$2:$E" & LRow)
  Set IndexTable = .Range("$E$2:$E" & LRow)
  End With

  With .Sheets(1) 'address list
  ALrow = .Cells(Rows.Count, 8).End(xlUp).Row
  ToFindMatch = .Range("$H2").Value
  End With

  For Each cell In .Sheets(1).Range("D2:D" & ALrow) 'address list
  cell.Formula = "=index(Sheet1!" & IndexTable.Address & ", Match(" & cell.Offset(0, 4).Address & ", Sheet1!" & MatchTable.Address & ",0))"
  Next
   
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

 

[KC E]

Try this modification:

Sub Test()

  Dim MatchTable, IndexTable As Range
  Dim LRow, ALrow, MatchAnsw As Long 'ALrow is address list last row
  Dim ToFindMatch As Variant
  Dim cell As Range
  
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  With ActiveWorkbook

  With .Sheets(2) 'billing
  LRow = .Cells(Rows.Count, 5).End(xlUp).Row
  Set MatchTable = .Range("$e$2:$E" & LRow)
  Set IndexTable = .Range("$E$2:$E" & LRow)
  End With

  With .Sheets(1) 'address list
  ALrow = .Cells(Rows.Count, 8).End(xlUp).Row
  ToFindMatch = .Range("$H2").Value
  End With

  For Each cell In .Sheets(1).Range("D2:D" & ALrow) 'address list
  cell.Formula = "=index(Sheet1!" & IndexTable.Address & ", Match(" & cell.Offset(0, 4).Address & ", Sheet1!" & MatchTable.Address & ",0))"
  Next
  
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Thank you for your reply. I tried running this code and a dialog box opened when I get to the 'cell.Formula = "=index', etc. named 'Update Values Sheet 1' and it stays stuck. If I press OK, it closes briefly and then pops back up.

I have to finally force shut down Excel. When I open the Address List back up, I get a few cells in column D with #N/A.

I also think looping will be an issue b/c I will have data that is sometimes 300,000 rows long. When I looped before, Excel froze.

Can you or someone else help? Thank you.

 

[KC E]

Try this modification:

Sub Test()

  Dim MatchTable, IndexTable As Range
  Dim LRow, ALrow, MatchAnsw As Long 'ALrow is address list last row
  Dim ToFindMatch As Variant
  Dim cell As Range
 
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  With ActiveWorkbook

  With .Sheets(2) 'billing
  LRow = .Cells(Rows.Count, 5).End(xlUp).Row
  Set MatchTable = .Range("$e$2:$E" & LRow)
  Set IndexTable = .Range("$E$2:$E" & LRow)
  End With

  With .Sheets(1) 'address list
  ALrow = .Cells(Rows.Count, 8).End(xlUp).Row
  ToFindMatch = .Range("$H2").Value
  End With

  For Each cell In .Sheets(1).Range("D2:D" & ALrow) 'address list
  cell.Formula = "=index(Sheet1!" & IndexTable.Address & ", Match(" & cell.Offset(0, 4).Address & ", Sheet1!" & MatchTable.Address & ",0))"
  Next
 
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

It works now. I added

cell.Formula = "=index(Sheet1!" & IndexTable.Address (True,True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", Sheet1!" & MatchTable.Address(True, True) & ",0))"

This makes the formula like =Index($E$2:$E$2,Match($H2,$E$2:$E$2,0)). There might have been some formatting issue where cells were a certain format or something, but now there is a match.

I tested against a Billing sheet with 73,000 rows and it pauses a little while but it is much faster than before. God bless you.