将A列和E列的excel值与B列和D列的名称匹配

yashar alinejad 发表于 Dev

亚沙尔·阿林贾德

基本上，我有一个巨大的excel文件，上面有我想为其指定名称的数字（我以UNKNOWN名称开头）

   A   |    B
123456 | Unknown  
456875 | Unknown2

为不同的行值分配一个序号。
如果列A2 =列E25，则用UNKNOWN2替换D25的值

那有意义吗？

我正在寻找一个可以自动执行的宏

伊里希尔

我已经很清楚地了解你了...

您在A列和D列中都有数字-对于每个要使用特定名称的数字。

名称应列在B列（对于A中的数字）和E列（对于D中的数字）。

如果A中的数字也与D中的数字相同，则这些数字具有相同的名称。

试试下面的代码：

Sub Test()

Dim rng As Range
Dim i As Long, j As Long, k As Long, l As Long
Dim firstAddress As String, foundAddress As String

For i = 1 To Rows.Count - 1

    If IsEmpty(Cells(i, 1).Value) Then Exit For

Next i

For j = 1 To Rows.Count - 1

    If IsEmpty(Cells(j, 4).Value) Then Exit For

Next j

Set rng = Range("A1:A" & i & ", D1:D" & j)

l = 1

For k = 1 To i + j

    If k < i Then

        firstAddress = rng.Find(Cells(l, 1).Value, Lookat:=xlWhole, MatchCase:=True).Address
        foundAddress = firstAddress

        If Cells(l, 2).Value = "" Then

            Do Until firstAddress = ""

                Cells(Range(foundAddress).Row, Range(foundAddress).Column + 1) = "Name" & k

                foundAddress = rng.Find(Cells(l, 1).Value, Range(foundAddress), Lookat:=xlWhole, MatchCase:=True).Address

                If foundAddress = firstAddress Then _
                    Exit Do

            Loop

        End If

    Else

        If k = i Then _
            l = 1

        firstAddress = rng.Find(Cells(l, 4).Value, Lookat:=xlWhole, MatchCase:=True).Address
        foundAddress = firstAddress

        If Cells(l, 5).Value = "" Then

            Do Until firstAddress = ""

                Cells(Range(foundAddress).Row, Range(foundAddress).Column + 1) = "Name" & k

                foundAddress = rng.Find(Cells(l, 4).Value, Range(foundAddress), Lookat:=xlWhole, MatchCase:=True).Address

                If foundAddress = firstAddress Then _
                    Exit Do

            Loop

        End If

        If l = j - 1 Then _
            Exit Sub

    End If

    l = l + 1

Next k

End Sub