Excel VBA 在选定范围内标准化名称

Question

此代码接受选择一个单元格，最多可选择一个连续单元格列
将范围复制到数组以提高效率
执行数组中发布代码中的所有替换
将更新后的数组放回到选定的范围内

Option Explicit

Public Sub MultiFindNReplace()

    Const LBLS  As String = "Labels to be updated "
    Const xNAME As String = "Name Update"

    Const OUT   As String = " .COM|.COM| INC.|INC.| INC | LTD |,|-|.|'"

    Const R1    As String = " AND|, LA|,LA| LTÉE| LTÉE.| LIMITÉE| LTD.| INCORPORATION|"
    Const R2    As String = " CORP.| CO.| & CO| AND CO| & CO.| CO. LTD| & CO INC|"
    Const R3    As String = " & CO., INC.| CO., INC.| CO (INC)| LTD| INC| SVC| CTR|"
    Const R4    As String = " LIMITED| LIMITED PARTNERSHIP| CO| LT| MD| OD| THE CO LTD|"
    Const R5    As String = " LTEE| LTEE CORP| CORP| INCORPORATED"

    Const RSIDE As String = R1 & R2 & R3 & R4 & R5

    Const L1    As String = "THE | THE|(THE) | (THE)|LE | LE|(LE) | (LE)|LES |"
    Const L2    As String = " LES|(LES) | (LES)|LA |(LA) |(L') "

    Const LSIDE As String = L1 & L2

    Dim inRng As Range, mAr As Variant, allRows As Long, i As Long, itm As Variant
    Dim outArr As Variant, rsArr As Variant, lsArr As Variant, sz1 As Long, sz2 As Long

    outArr = Split(OUT, "|")
    rsArr = Split(RSIDE, "|")
    lsArr = Split(LSIDE, "|")

    Set inRng = Application.Selection
    Set inRng = Application.InputBox(LBLS, xNAME, inRng.Address, Type:=8)

    If inRng.Columns.Count > 1 Or inRng.Areas.Count > 1 Then
        MsgBox "Please select a single (contiguous) column"
        Exit Sub
    End If

    allRows = inRng.Rows.Count
    MsgBox allRows & " rows Selected"

    If inRng.Count = 1 Then     'if only one cell selected force mAr to array
        ReDim mAr(1, 1)
        mAr(1, 1) = inRng.Value2
    Else
        mAr = inRng.Value2
    End If

    For i = 1 To allRows

       For Each itm In outArr   'remove all occurences of "itm"
         mAr(i, 1) = Replace(mAr(i, 1), itm, vbNullString, , , vbTextCompare)
       Next

       mAr(i, 1) = Replace(mAr(i, 1), "&", "AND")  'replace "&" with "AND"

       For Each itm In rsArr    'remove trailing "itm"
         sz1 = Len(itm)
         sz2 = Len(mAr(i, 1))
         If Right(mAr(i, 1), sz1) = itm Then mAr(i, 1) = Left(mAr(i, 1), sz2 - sz1)
       Next

       For Each itm In lsArr    'remove leading "itm"
         sz1 = Len(itm)
         sz2 = Len(mAr(i, 1))
         If Left(mAr(i, 1), Len(itm)) = itm Then mAr(i, 1) = Right(mAr(i, 1), sz2 - sz1)
       Next

    Next

    inRng = mAr                 'place memory array back to range
    MsgBox "Finished"

End Sub

笔记：

我将所有硬编码值移至子顶部的常量中，以便于维护

（我想我添加了其中几个 - 请检查并删除您不需要的）

Answer 1

此代码接受选择一个单元格，最多可选择一个连续单元格列
将范围复制到数组以提高效率
执行数组中发布代码中的所有替换
将更新后的数组放回到选定的范围内

Option Explicit

Public Sub MultiFindNReplace()

    Const LBLS  As String = "Labels to be updated "
    Const xNAME As String = "Name Update"

    Const OUT   As String = " .COM|.COM| INC.|INC.| INC | LTD |,|-|.|'"

    Const R1    As String = " AND|, LA|,LA| LTÉE| LTÉE.| LIMITÉE| LTD.| INCORPORATION|"
    Const R2    As String = " CORP.| CO.| & CO| AND CO| & CO.| CO. LTD| & CO INC|"
    Const R3    As String = " & CO., INC.| CO., INC.| CO (INC)| LTD| INC| SVC| CTR|"
    Const R4    As String = " LIMITED| LIMITED PARTNERSHIP| CO| LT| MD| OD| THE CO LTD|"
    Const R5    As String = " LTEE| LTEE CORP| CORP| INCORPORATED"

    Const RSIDE As String = R1 & R2 & R3 & R4 & R5

    Const L1    As String = "THE | THE|(THE) | (THE)|LE | LE|(LE) | (LE)|LES |"
    Const L2    As String = " LES|(LES) | (LES)|LA |(LA) |(L') "

    Const LSIDE As String = L1 & L2

    Dim inRng As Range, mAr As Variant, allRows As Long, i As Long, itm As Variant
    Dim outArr As Variant, rsArr As Variant, lsArr As Variant, sz1 As Long, sz2 As Long

    outArr = Split(OUT, "|")
    rsArr = Split(RSIDE, "|")
    lsArr = Split(LSIDE, "|")

    Set inRng = Application.Selection
    Set inRng = Application.InputBox(LBLS, xNAME, inRng.Address, Type:=8)

    If inRng.Columns.Count > 1 Or inRng.Areas.Count > 1 Then
        MsgBox "Please select a single (contiguous) column"
        Exit Sub
    End If

    allRows = inRng.Rows.Count
    MsgBox allRows & " rows Selected"

    If inRng.Count = 1 Then     'if only one cell selected force mAr to array
        ReDim mAr(1, 1)
        mAr(1, 1) = inRng.Value2
    Else
        mAr = inRng.Value2
    End If

    For i = 1 To allRows

       For Each itm In outArr   'remove all occurences of "itm"
         mAr(i, 1) = Replace(mAr(i, 1), itm, vbNullString, , , vbTextCompare)
       Next

       mAr(i, 1) = Replace(mAr(i, 1), "&", "AND")  'replace "&" with "AND"

       For Each itm In rsArr    'remove trailing "itm"
         sz1 = Len(itm)
         sz2 = Len(mAr(i, 1))
         If Right(mAr(i, 1), sz1) = itm Then mAr(i, 1) = Left(mAr(i, 1), sz2 - sz1)
       Next

       For Each itm In lsArr    'remove leading "itm"
         sz1 = Len(itm)
         sz2 = Len(mAr(i, 1))
         If Left(mAr(i, 1), Len(itm)) = itm Then mAr(i, 1) = Right(mAr(i, 1), sz2 - sz1)
       Next

    Next

    inRng = mAr                 'place memory array back to range
    MsgBox "Finished"

End Sub

笔记：

我将所有硬编码值移至子顶部的常量中，以便于维护

（我想我添加了其中几个 - 请检查并删除您不需要的）

Excel VBA 在选定范围内标准化名称

答案1

相关内容