title: Update table values from another Table
body: <p>I have a workbook with a table that is roughly 20000 rows in size and 52 columns. At times, I need to update a percentage of select rows at once. I'm hoping to use a macro to update the select cells based on a value in the row, mapped out by a second smaller table with the updated values to be entered in to table 1. Almost like a VLOOKUP function, but one that doesn't erase the cell if the entry isn't found. For example, change the Phone Number according to the Host ID.</p>
  
  <p>I tried to do this with an Array in the code below for a specfic set of the values in Table 1, but my values didn't update. My VBA is a bit rusty, so if someone can review and assist with getting this to function, it would be appreciated. I would like to make it update any entry in the table based on the table headers eventually.</p>
  
  <pre><code>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" &amp; 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
  </code></pre>
  
  <p>Thanks,
  JD</p>