Update table values ​​from another table

Question

I have a book with a table about 20000 rows in size and 52 columns. From time to time, I need to update the percentage of selected rows immediately. I hope to use a macro to update the selected cells based on the value in the row displayed by the second smaller table with the updated values ​​that should be entered in table 1. Almost like a VLOOKUP function, but one that doesn't work doesn’t erase the cell if no entry is found . For example, change the phone number to match the host ID.

I tried to do this using an array in the code below for a specific set of values ​​in table 1, but my values ​​were not updated. My VBA is a bit rusty, so if someone can look over and help him get it to work, it will be appreciated. I would like it to update any record in the table based on the table headers in the end.

Sub NewNameandCostCenter() Dim myList, myRange Dim sht As Worksheet Dim sht2 As Worksheet Dim LastRow As Long Dim LastColumn As Long Dim StartCell As Range Dim LastRow2 As Long Set sht = Worksheets("NewNameMacro") Set sht2 = Worksheets("ALL") Set StartCell = Range("A2") 'Find Last Row and Column LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column 'set myList array Set myList = sht.Range(StartCell, sht.Cells(LastRow, LastColumn)) LastRow2 = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'set myRange array Set myRange = Sheets("ALL").Range("J2:M" & LastRow2) 'Update values of cells adjacent For Each cel In myList.Columns(1).Cells myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 2).Value, LookAt:=xlWhole myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 3).Value, LookAt:=xlWhole Next cel End Sub 

Thanks JD

Answer 1

If I understand your question correctly, you effectively run an UPDATE query on your data based on the values ​​in your mapping table.

I assumed the following:

• The key column is the first column in your data table and in the mapping table.

• The columns in the mapping table are in the same order and relative position as the columns in the data table (although this can be easily customized.

• The order of the keys in the mapping table and data table is not sorted. If you can make sure the keys are sorted (ideally on both sheets), you can achieve significantly better performance with a few changes.

I hardcoded the ranges in my example, but you can restore the last row and last column approach if you need to.

I did all my comparisons between arrays, not ranges, and I ended up searching. You will find that it works and works much more efficiently.

 Option Explicit Sub NewNameandCostCenter() Dim start As Double start = Timer Dim countOfChangedRows As Long 'set rngMap array Dim rngMap As Range Set rngMap = Worksheets("Map").Range("A1:D51") 'set rngData array Dim rngData As Range Set rngData = Worksheets("Data").Range("J2:M20001") Dim aMap As Variant aMap = rngMap.Value Dim aData As Variant aData = rngData.Value Dim mapRow As Long Dim datarow As Long Dim mapcol As Long For mapRow = LBound(aMap, 1) To UBound(aMap, 1) For datarow = LBound(aData) To UBound(aData) 'Check the key matches in both tables If aData(datarow, 1) = aMap(mapRow, 1) Then countOfChangedRows = countOfChangedRows + 1 'Assumes the columns in map and data match For mapcol = LBound(aMap, 2) + 1 To UBound(aMap, 2) aData(datarow, mapcol) = aMap(mapRow, mapcol) Next mapcol End If Next datarow Next mapRow rngData.Value = aData Debug.Print countOfChangedRows & " of "; UBound(aData, 1) & " rows updated in " & Timer - start & " seconds" End Sub 

Performance is reasonable for 50 updated lines:

50 of 20000 rows updated in 0.23828125 seconds

But if you need to start updating thousands of rows, then it will be very useful for you to sort the data and set it up.