Mã:
Public Sub Ma_Hoa_Ten_Sach()
Dim DL, MaVan, XoaDau, Tam, r As Long, rw As Long, c As Long, i, j
DL = Sheet2.Range("A2", Sheet2.Range("A65000").End(xlUp))
MaVan = Sheet1.Range("A1").CurrentRegion
XoaDau = Sheet1.Range("D1").CurrentRegion
For r = 1 To UBound(DL)
Tam = Split(DL(r, 1) & " ", " ")
DL(r, 1) = Tam(0) & " " & Tam(1)
For c = 1 To Len(DL(r, 1))
For rw = 1 To UBound(XoaDau)
If Mid(DL(r, 1), c, 1) = XoaDau(rw, 1) Then
Mid(DL(r, 1), c, 1) = XoaDau(rw, 2)
End If
Next rw
Next c
Tam = Split(DL(r, 1), " ")
i = Len(Tam(0)): j = ""
For rw = 1 To UBound(MaVan)
If InStr(1, Tam(0), MaVan(rw, 1), 1) Then
If i > Len(Replace(Tam(0), MaVan(rw, 1), "")) Then
i = Len(Replace(Tam(0), MaVan(rw, 1), ""))
j = MaVan(rw, 2)
End If
End If
Next rw
Tam(0) = UCase(Left(Tam(0), i) & j)
i = Len(Tam(1))
For rw = 1 To UBound(MaVan)
If InStr(1, Tam(1), MaVan(rw, 1), 1) Then
If i > Len(Replace(Tam(1), MaVan(rw, 1), "")) Then
i = Len(Replace(Tam(1), MaVan(rw, 1), ""))
End If
End If
Next rw
Tam(1) = UCase(Left(Tam(1), i))
DL(r, 1) = Tam(0) & Tam(1)
Next r
Sheet2.Range("B2").End(xlDown).ClearContents
Sheet2.Range("B2").Resize(UBound(DL), 1).Value = DL
End Sub
Trả lời kèm Trích dẫn
Gửi bởi hoainam1301
