Vlookup9
Sub santo()
Dim LastRow As Long
Set ws = Worksheets(“Customer”)
LastRow = Cells.Find(“*”, , xlValues, , xlRows, xlPrevious).Row
wrk = Array(Worksheets(“MIDDLE_INITIAL”), Worksheets(“LAST_NAME”))
na = Array(“old”, “new”)
a = Array(“I”, “J”, “K”, “F”)
For j = 0 To 1
For i = 0 To 2
With ws
.Range(a(i) & “1”).EntireColumn.Insert
If i > 1 Then
.Range(a(i) & “1”).Value = na(j) + ” ” + “Fullname”
For b = 2 To LastRow
ws.Cells(b, a(2)).Value = ws.Cells(b, a(3)).Value + ” ” + ws.Cells(b, a(0)).Value + ” ” + ws.Cells(b, a(1)).Value
Next b
Else
.Range(a(i) & “1”).Value = na(j) + ” ” + Sheets(3 + i).Name
.Range(a(i) & “2”, a(i) & LastRow) = Application.WorksheetFunction.VLookup(ws.Range(“B2:B” & LastRow), wrk(j).Range(“A:F”), (6 + i), False)
.Range(a(i) & “2”, a(i) & LastRow).Replace what:=”#N/A”, Replacement:=” “
End If
End With
Next i
a = Array(“M”, “N”, “O”, “L”)
Next j
End Sub
Sub santo()
Dim LastRow As Long
Set ws = Worksheets(“Customer”)
LastRow = Cells.Find(“*”, , xlValues, , xlRows, xlPrevious).Row
wrk = Array(Worksheets(“MIDDLE_INITIAL”), Worksheets(“LAST_NAME”))
na = Array(“old”, “new”)
a = Array(“I”, “J”, “K”, “F”)
For j = 0 To 1
For i = 0 To 2
With ws
.Range(a(i) & “1”).EntireColumn.Insert
If i > 1 Then
.Range(a(i) & “1”).Value = na(j) + ” ” + “Fullname”
For b = 2 To LastRow
ws.Cells(b, a(2)).Value = ws.Cells(b, a(3)).Value + ” ” + ws.Cells(b, a(0)).Value + ” ” + ws.Cells(b, a(1)).Value
Next b
Else
.Range(a(i) & “1”).Value = na(j) + ” ” + Sheets(3 + i).Name
.Range(a(i) & “2”, a(i) & LastRow) = Application.WorksheetFunction.VLookup(ws.Range(“B2:B” & LastRow), wrk(j).Range(“A:F”), (6 + i), False)
.Range(a(i) & “2”, a(i) & LastRow).Replace what:=”#N/A”, Replacement:=” “
End If
End With
Next i
a = Array(“M”, “N”, “O”, “L”)
Next j
End Sub Read More
