我需要进行大量查找和替换,我可以使用“替代”表来完成吗?

我需要进行大量查找和替换,我可以使用“替代”表来完成吗?

我有一个包含患者和医院的主表。医院有拼写错误,其中一些没有冠词,如下所示。

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

相关内容