Thank you Carim!
I tested it, and I noticed there are 2 things I want to discuss with you:
1. When I tried, it did not allow me to update the blank cells - instead, it replaces all.
(For example, when I want to just update Phone Number and Mobile Number for Ellen, it removes all previous information and only adds Phone Number and Mobile Number.)
I will just remove my request to replace cells with new data in "Template" sheet, but want to change to 'update blank cells only'.
Can you please help me to generate macro code to update blank cells only?
2. I want to change the Macro to compare First Name and Last Name in "Template" sheet with the First Name and Last Name in "Contact Details" sheet.
I am attaching my previous data and code again for your help.
Thank you!
- Sub Test2()
- Dim lookUpSheet As Worksheet, updateSheet As Worksheet
- Dim valueToSearch As String
- Dim i As Integer, j As Integer, t As Integer
- Dim lastRowLookup As Long, lastColumnUpdate As Long
- Dim found As Range
- Set lookUpSheet = Sheets("Template")
- Set updateSheet = Sheets("Contact Details")
- 'get the number of the last row with data in Contact Details
- lastRowLookup = updateSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
- 'get the number of the last column with data in Template
- lastColumnUpdate = lookUpSheet.Cells(1, Columns.Count).End(xlToLeft).Column
- 'for First name Template
- ' Loop All Columns in Sheet2 Template
- For j = 2 To lastColumnUpdate
- valueToSearch = lookUpSheet.Cells(2, j) And lookUpSheet.Cells(3, j)
- 'look the value in Column B of Contact Details
- Set found = updateSheet.Columns("B:B").Find(What:=valueToSearch, After:=updateSheet.Range("B1:B2"), LookIn:=xlValues, _
- LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
- MatchCase:=False, SearchFormat:=False)
-
- If found Is Nothing Then
- ' Not Found
- updateSheet.Range("A" & lastRowLookup & ":K" & lastRowLookup).Value = WorksheetFunction.Transpose(Range(lookUpSheet.Cells(1, j), lookUpSheet.Cells(11, j)))
- Else
- ' Found
- i = found.Row
- updateSheet.Range("A" & i & ":K" & i).Value = WorksheetFunction.Transpose(Range(lookUpSheet.Cells(1, j), lookUpSheet.Cells(11, j)))
- End If
- Next j
- End Sub
Display More