我有一个包含患者和医院的主表。医院有拼写错误,其中一些没有冠词,如下所示。
Name Hospital
----------------------------
Fulano Hospital Princesa
Mengano Princesa
Futano La PRincesa
Octano Paz
Nonano La Paz
Zipi PAz
我需要将所有“Princesa”单元格更改为“Hospital La Princesa”,将所有“La Paz”单元格更改为“Hospital La Paz”。
每次我修正数据库时,我都会手动进行“查找和替换”(“公司“-> “公主医院””和“亚利桑那州“-> “拉巴斯医院”)
因为医院太多(接近 15 家)并且主表中的行太多,所以我想知道是否有简单的方法可以做到这一点(也许使用 vlookup,也许使用 VBA)
答案1
尝试这个:
Option Explicit
Public Sub FixHospitalNames()
Dim ws As Worksheet, itm As Variant, i As Long
Dim hOld As Variant, hNew As Variant, extras As Variant
extras = Split("hospital,la, ", ",") 'removes any extras (splits string by ",")
hOld = Split("princesa paz") 'replace these 2 items with the 2 items from hNew
hNew = Split("Hospital La Princesa,Hospital La Paz", ",")
With Sheet1.UsedRange.Columns(2).Offset(1) 'Update Sheet and Column as needed
For Each itm In extras
.Replace What:=itm, Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=False
Next
For i = 0 To UBound(hOld)
.Replace What:=hOld(i), Replacement:=hNew(i), LookAt:=xlPart, MatchCase:=False
Next
End With
End Sub