none
Excel 2013 VBA: Index/Match Does Not Show a Match Between WkShts RRS feed

  • Question

  • Hello,

    I have the code below to add an address to the Address wksht if it matches the address in the Billing wksht. It matches addresses when I use a brand new workbook with an Address and a Billing wksht, but when I use the workbook I actually need to use, the addresses do not match properly.

    Actual workbook:  Both the Address and Billing wkshts are downloaded from 3rd party programs. The Address data is in the 1st sheet and the Billing data is copied to the workbook in the 2nd sheet (I haven't added the code to do that yet).

    The wkshts both have many columns and have can have anywhere from 60,000 to 300,000 rows. The Address wksht, though, will probably only have at the most 1,000, I believe.

    Test workbook: I commented out each block of code to see what might be causing the issue in the test workbook and none of the code caused an issue (b/c I have some code to insert column, add headers, concatenate/substitute/trim the addresses, sort columns BEFORE I use Index/Match).

    When I test on the brand new workbook, I'm not sure if it's b/c it is brand new or if it is b/c there are not many rows b/c I only added 5 rows to test it. Can anyone help? If it is number rows, I don't know how to fix that b/c that is the way the data comes, but if it is a matter of the cleanliness of the wkshts, is there a way I can clean the wksht before running the Index/Match?

    *Edit:*

    How it works: After the Address and Billing wkshts are prepared (adding of columns, etc., described above and in Formatting section of code), the concatenated address from the 'Helper Address' column E of the Billing wksht (2nd wksht) is matched against the 'Helper Address' in column H of the Address wksht (1st wksht). If there is a match, then the Index/Match formula will populate the address in the 'Address' column D of the Address wksht.

    The Index/Match formula translates to: IFERROR(INDEX(Sheet2!$E$2:$E$69214, MATCH($H2, Sheet2!$E$2:$E$69214,0)), "")

    *End Edit*

    (Also, one of the things is the Edit Links box pops up with I open workbooks that have had this code run in it, if needs to be another post on how to stop that, then that is ok.)

    Thank you in advance for any help.

    Sub Test()
    'THIS
      Dim MatchTable, IndexTable As Range
      Dim LRow, ALrow As Long 'ALrow is address list last row
      Dim ToFindMatch As String
      Dim cell As Range
       
      'Application.Calculation = xlCalculationManual 'turned off b/c won't fill down substitute/trim formula
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.DisplayAlerts = False 'helps turn off 'Update Values:Sheet 1' dialog box that kept popping up
      
    With ActiveWorkbook
      
      '---FORMATTING
      With .Sheets(1) 'address list
        ALrow = .Cells(Rows.Count, 4).End(xlUp).Row
        Columns("A:D").Insert shift:=xlToRight
        Range("A1").Value = "Node" 'answer column, not finished code for this yet
        Range("B1").Value = "Bridger" 'answer column, not finished code for this yet
        Range("C1").Value = "Housekey" 'answer column, not finished, etc.
        Range("D1").Value = "Address" 'answer column where matched address will go from Billing wksht
        Range("A1:D1").Font.Bold = True
        Range("A1:D1").Interior.Color = RGB(255, 242, 204)
    '    'Range("C:C").NumberFormat = "@" 'make housekey column text so housekeys not change to sci notation
    
        Columns("H:H").Insert shift:=xlToRight
        Range("H1").Value = "Helper Address" 'insert helper column
        Range("H1").Font.Bold = True
        Range("H1").Interior.Color = RGB(255, 242, 204)
    
        'concatenate address and trim spaces
        Range("H2").Formula = "=SUBSTITUTE(TRIM(I2) & TRIM(K2) & TRIM(M2) & TRIM(N2), "" "","""")"
        'Range("H2").FormulaR1C1 = "=SUBSTITUTE(TRIM(RC[1]) & TRIM(RC[3])&TRIM(RC[5])&TRIM(RC[6]), "" "","""")"
        Range("H2").AutoFill Destination:=Range("H2:H" & ALrow)
        Range("H2:H" & ALrow).Copy
        Range("H2:H" & ALrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("H:H").EntireColumn.AutoFit
      
        'Sort address list - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("M2:M" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("I2:I" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("F2:F" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(1).sort
                .SetRange Range("A1:N" & ALrow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
      Worksheets(2).Select
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
        Columns("E:E").Insert shift:=xlToRight
        Range("E1").Value = "Helper Address" 'insert helper address
        Range("E1").Font.Bold = True
        Range("E1").Interior.Color = RGB(255, 242, 204)
    
        'concatenate address and trim spaces
        Range("E2").Select
        Range("E2").Formula = "=SUBSTITUTE(TRIM(B2)&TRIM(C2),"" "","""")"
        Range("E2").AutoFill Destination:=Range("E2:E" & LRow)
        Range("E2:E" & LRow).Copy
        Range("E2:E" & LRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("E:E").EntireColumn.AutoFit
    
        'Sort billing - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("C2:C" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("B2:B" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("D2:D" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(2).sort
                .SetRange Range("A1:E" & LRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
       Worksheets(1).Select 'go back to address wksht
       
    '---END FORMATTING
       
       
    '---CALCULATION - INDEX/MATCH
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set MatchTable = .Range("E2:E" & LRow)
        Set IndexTable = .Range("E2: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 = "=iferror(index(Sheet2!" & IndexTable.Address(True, True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", Sheet2!" & MatchTable.Address(True, True) & ",0)),"""")" 'blank "" in double quotes """"
      Next cell
    
    
    End With
    
    
      'Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.DisplayAlerts = True
    
       
    
    End Sub
    
    




    • Edited by 123Group456 Thursday, October 19, 2017 12:00 AM Add 'Edit' section of how code works and what Index/Match formula translate into.
    Wednesday, October 18, 2017 9:57 PM

Answers

  • Mr. Cone, 

    I worked around the problem by adding code to a) rename the billing worksheet to 'billing', and then b) to refer to that name in the Index/Match code instead of referring to the code name of the worksheet, 'Sheet1!'.

    a) 

     ActiveSheet.Name = "billing"

    b)

     cell.Formula = "=iferror(index(billing!" & IndexTable.Address(True, True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", billing!" & MatchTable.Address(True, True) & ",0)),"""")" 'blank "" in double quotes """"
    

    This works. I still don't know why using 'Sheet1!' in the Index/Match code worked on the small Test workbook but not on the large original workbook, but I had to workaround it and go this route.

    I did not realize that's what the '.' before the code was associating it with the sheet listed in the With/End With statements. Thank you for your help. God bless you -

    Sub Test()
    'THIS
      Dim MatchTable, IndexTable As Range
      Dim LRow, ALrow As Long 'ALrow is address list last row
      Dim ToFindMatch As String
      Dim cell As Range
       
      'Application.Calculation = xlCalculationManual 'turned off b/c won't fill down substitute/trim formula
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.DisplayAlerts = False 'helps turn off 'Update Values:Sheet 1' dialog box that kept popping up
      
    With ActiveWorkbook
      
      '---FORMATTING
      With .Sheets(1) 'address list
        ALrow = .Cells(Rows.Count, 4).End(xlUp).Row
        Columns("A:D").Insert shift:=xlToRight
        Range("A1").Value = "Node" 'answer column
        Range("B1").Value = "Bridger" 'answer column
        Range("C1").Value = "Housekey" 'answer column
        Range("D1").Value = "Address" 'answer column
        Range("A1:D1").Font.Bold = True
        Range("A1:D1").Interior.Color = RGB(255, 242, 204)
    '    'Range("C:C").NumberFormat = "@" 'make housekey column text so housekeys not change to sci notation
    
        Columns("H:H").Insert shift:=xlToRight
        Range("H1").Value = "Helper Address" 'insert helper column
        Range("H1").Font.Bold = True
        Range("H1").Interior.Color = RGB(255, 242, 204)
    
        'concatenate address and trim spaces
        Range("H2").Formula = "=SUBSTITUTE(TRIM(I2) & TRIM(K2) & TRIM(M2) & TRIM(N2), "" "","""")"
        'Range("H2").FormulaR1C1 = "=SUBSTITUTE(TRIM(RC[1]) & TRIM(RC[3])&TRIM(RC[5])&TRIM(RC[6]), "" "","""")"
        Range("H2").AutoFill Destination:=Range("H2:H" & ALrow)
        Range("H2:H" & ALrow).Copy
        Range("H2:H" & ALrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("H:H").EntireColumn.AutoFit
      
        'Sort address list - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("M2:M" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("I2:I" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("F2:F" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(1).sort
                .SetRange Range("A1:N" & ALrow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
      Worksheets(2).Select
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
        Columns("E:E").Insert shift:=xlToRight
        Range("E1").Value = "Helper Address" 'insert helper address
        Range("E1").Font.Bold = True
        Range("E1").Interior.Color = RGB(255, 242, 204)
        ActiveSheet.Name = "billing"
    
        'concatenate address and trim spaces
        Range("E2").Select
        Range("E2").Formula = "=SUBSTITUTE(TRIM(B2)&TRIM(C2),"" "","""")"
        Range("E2").AutoFill Destination:=Range("E2:E" & LRow)
        Range("E2:E" & LRow).Copy
        Range("E2:E" & LRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("E:E").EntireColumn.AutoFit
    
        'Sort billing - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("C2:C" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("B2:B" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("D2:D" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(2).sort
                .SetRange Range("A1:E" & LRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
       Worksheets(1).Select 'go back to address wksht
       
    '---END FORMATTING
       
       
    '---CALCULATION - INDEX/MATCH
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set MatchTable = .Range("E2:E" & LRow)
        Set IndexTable = .Range("E2: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 = "=iferror(index(billing!" & IndexTable.Address(True, True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", billing!" & MatchTable.Address(True, True) & ",0)),"""")" 'blank "" in double quotes """"
      Next cell
    
      With .Sheets(1) 'address list
        Range("D2:D" & ALrow).Copy
        Range("D2:D" & ALrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("D:D").EntireColumn.AutoFit
      End With
    
    End With
    
      'Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.DisplayAlerts = True
       
    
    End Sub

    • Marked as answer by 123Group456 Monday, October 23, 2017 10:34 PM
    Monday, October 23, 2017 10:34 PM

All replies

  • 123,
    re: "is there a way I can clean the wksht before running the Index/Match"

    Cleaning is the first thing to do when using data from outside sources.

    Data can be contaminated with characters that are not visible and that feeds the notion that the data is OK.
    The invisible, non-breaking space (character 160), is the most infamous.
    Numbers with trailing minus signs and text with multiple interior spaces contribute.

    The VBA Trim functions do not remove excess interior spaces from text, while the Excel Trim function does.
    The Excel Clean function can remove a lot of non-visible characters and using the VBA.Cdbl function will convert trailing minus signs.
    Character 160 can be removed using the Excel find/replace utility.  Make sure to replace it with a space (character 32).
    Also, dashes separating sections of the data are helpfully interpreted as part of a formula by Excel and result in errors.

    It takes me about 4 pages of typed vba code to automate all this. (included it in a couple of programs, I attempt to sell).

    If you run the Excel Clean and Trim functions in helper columns and replace character 160 you may be ok.

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



    Thursday, October 19, 2017 7:31 PM
  • Hello, thank you for your reply.

    I don't think it is b/c the spreadsheet is not clean after testing some code I found to thoroughly clean the data, even cleaning it manually using CLEAN and TRIM like you mention. P.S. your add ins look neat in your dropbox.

    I tried-

    1. I tried using =IF(CLEAN(A1)=A1,"NA","Needs Cleaning") to test if it needed to be cleaned (1 Source below) and it returned 'NA', meaning it did not need cleaning.
    2. I tried using code (2) to clean column D in the Address wksht where the Index/Match answer would be applied. Then using my code--just the part with Index/Match--and it did not find a match.
    3. I tried used UDF (3)  by manually entering CleanTrim() UDF on column D in the Address wksht and then running the Index/Match part of the code and it did not find a match.
    4. I've tried changing the 'ToFindMatch' variable to Variant data type.
    5. I tried changing the Index/Match part of the code to Sheet1! thinking that the worksheet code name is really Sheet1! for the Billing wksht, but you can look at the Project Window and see the code name of the Billing wksht is Sheet2!

     

    Also the fact, that I can manually add the Index/Match function to the column D of the Address wksht and find a match from column E in the Billing wkbk means to me I don't need to clean my wkbk.

    By the way, this is Sheet1 the Address List (the 1st tab in the wkbk) column D is where the Index/Match formula goes:

    This is Sheet2 the Billing (the 2nd tab in the wkbk) column H above matches against column E below:

     

     2 things I think now:

    1. My Index/Match code is wrong, and if it is, I think it is part Sheet2! part of the code. Maybe it's just not picking it up, although why would it work when I use my small test workbook?
    2. I have seen posts on the internet where some say when you have a lot of rows of data, it just doesn't work. I don't know why that would be, though. That is why I changed the variable type in 4. above thinking it was a problem.

    I want to say, I'm not an expert, I'm learning, but I would appreciate any help from anyone b/c all works except the Index/Match part. Thank you for your help, Mr. Cone.

    Sources: 

    1) Clean test function - https://superuser.com/questions/529585/how-to-display-or-view-non-printing-characters-in-excel

    2) Clean Area - https://www.thespreadsheetguru.com/the-code-vault/a-fast-way-to-clean-trim-cell-values-with-vba-code

    3) UDF - http://www.excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092



    • Edited by 123Group456 Saturday, October 21, 2017 12:30 AM
    Saturday, October 21, 2017 12:27 AM
  • 123,
    re: washed but still dirty

    Appreciate you taking a look at my programs at Dropbox.

    The initial part of your "Test" sub is dependant on what sheet happens to be selected when the code starts.
    The cell, columns, ranges are not desiginated as belonging to Sheets(1) because there is no dot connecting them to the With statement.
    I recommend cleaning up the entire code set - check every line - before attempting to resolve your index/match issues.
    '---
    Jim Cone

    'Sample below...
    '---
    With ActiveWorkbook 
      With .Sheets(1) 'address list
        ALrow = .Cells(Rows.Count, 4).End(xlUp).Row
        Columns("A:D").Insert shift:=xlToRight
        Range("A1").Value = "Node" 'answer column, not finished code for this yet
        Range("B1").Value = "Bridger" 'answer column, not finished code for this yet
    '---

    Saturday, October 21, 2017 2:22 AM
  • Mr. Cone, 

    I worked around the problem by adding code to a) rename the billing worksheet to 'billing', and then b) to refer to that name in the Index/Match code instead of referring to the code name of the worksheet, 'Sheet1!'.

    a) 

     ActiveSheet.Name = "billing"

    b)

     cell.Formula = "=iferror(index(billing!" & IndexTable.Address(True, True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", billing!" & MatchTable.Address(True, True) & ",0)),"""")" 'blank "" in double quotes """"
    

    This works. I still don't know why using 'Sheet1!' in the Index/Match code worked on the small Test workbook but not on the large original workbook, but I had to workaround it and go this route.

    I did not realize that's what the '.' before the code was associating it with the sheet listed in the With/End With statements. Thank you for your help. God bless you -

    Sub Test()
    'THIS
      Dim MatchTable, IndexTable As Range
      Dim LRow, ALrow As Long 'ALrow is address list last row
      Dim ToFindMatch As String
      Dim cell As Range
       
      'Application.Calculation = xlCalculationManual 'turned off b/c won't fill down substitute/trim formula
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.DisplayAlerts = False 'helps turn off 'Update Values:Sheet 1' dialog box that kept popping up
      
    With ActiveWorkbook
      
      '---FORMATTING
      With .Sheets(1) 'address list
        ALrow = .Cells(Rows.Count, 4).End(xlUp).Row
        Columns("A:D").Insert shift:=xlToRight
        Range("A1").Value = "Node" 'answer column
        Range("B1").Value = "Bridger" 'answer column
        Range("C1").Value = "Housekey" 'answer column
        Range("D1").Value = "Address" 'answer column
        Range("A1:D1").Font.Bold = True
        Range("A1:D1").Interior.Color = RGB(255, 242, 204)
    '    'Range("C:C").NumberFormat = "@" 'make housekey column text so housekeys not change to sci notation
    
        Columns("H:H").Insert shift:=xlToRight
        Range("H1").Value = "Helper Address" 'insert helper column
        Range("H1").Font.Bold = True
        Range("H1").Interior.Color = RGB(255, 242, 204)
    
        'concatenate address and trim spaces
        Range("H2").Formula = "=SUBSTITUTE(TRIM(I2) & TRIM(K2) & TRIM(M2) & TRIM(N2), "" "","""")"
        'Range("H2").FormulaR1C1 = "=SUBSTITUTE(TRIM(RC[1]) & TRIM(RC[3])&TRIM(RC[5])&TRIM(RC[6]), "" "","""")"
        Range("H2").AutoFill Destination:=Range("H2:H" & ALrow)
        Range("H2:H" & ALrow).Copy
        Range("H2:H" & ALrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("H:H").EntireColumn.AutoFit
      
        'Sort address list - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("M2:M" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("I2:I" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("F2:F" & ALrow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(1).sort
                .SetRange Range("A1:N" & ALrow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
      Worksheets(2).Select
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
        Columns("E:E").Insert shift:=xlToRight
        Range("E1").Value = "Helper Address" 'insert helper address
        Range("E1").Font.Bold = True
        Range("E1").Interior.Color = RGB(255, 242, 204)
        ActiveSheet.Name = "billing"
    
        'concatenate address and trim spaces
        Range("E2").Select
        Range("E2").Formula = "=SUBSTITUTE(TRIM(B2)&TRIM(C2),"" "","""")"
        Range("E2").AutoFill Destination:=Range("E2:E" & LRow)
        Range("E2:E" & LRow).Copy
        Range("E2:E" & LRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("E:E").EntireColumn.AutoFit
    
        'Sort billing - via macro
       .sort.SortFields.Clear
       .sort.SortFields.Add Key:=Range("C2:C" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("B2:B" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       .sort.SortFields.Add Key:=Range("D2:D" & LRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With Worksheets(2).sort
                .SetRange Range("A1:E" & LRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
         With ActiveWindow 'freeze top row
          .SplitColumn = 0
          .SplitRow = 1
          .FreezePanes = True
         End With
      End With
       
       Worksheets(1).Select 'go back to address wksht
       
    '---END FORMATTING
       
       
    '---CALCULATION - INDEX/MATCH
      With .Sheets(2) 'billing
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set MatchTable = .Range("E2:E" & LRow)
        Set IndexTable = .Range("E2: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 = "=iferror(index(billing!" & IndexTable.Address(True, True) & ", Match(" & cell.Offset(0, 4).Address(False, True) & ", billing!" & MatchTable.Address(True, True) & ",0)),"""")" 'blank "" in double quotes """"
      Next cell
    
      With .Sheets(1) 'address list
        Range("D2:D" & ALrow).Copy
        Range("D2:D" & ALrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("D:D").EntireColumn.AutoFit
      End With
    
    End With
    
      'Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Application.DisplayAlerts = True
       
    
    End Sub

    • Marked as answer by 123Group456 Monday, October 23, 2017 10:34 PM
    Monday, October 23, 2017 10:34 PM