vba - 当多个数据单元格具有等效的标识单元格时，如何用同一单元格替换它们？

Question

所以我对 VBA 非常陌生，所以我为我糟糕的编码表示歉意。我花了几个小时寻找这个看似简单问题的答案，我可能已经遇到过它，但只是不明白它是什么。

本质上，我想做的是使用识别变量对一组变量进行分类。我拥有的数据看起来像相册中的顶部图像，而我需要的数据看起来像相册中的第二张图像(除了我有超过 20,000 点)。如果有人能帮助我，我将非常感激。

到目前为止我已经:

Sub Try_3()
SessionID_Cell = Value.Range("D3:D10047")
SessionID_Change = Value.Range("I2:I5748")
Name_Change = Value.Range("J2:J5748")

For Each SessionID_Cell In Range("D2:D10047")

If SessionID_Cell = SessionID_Change Then
SessionID_Cell.Value = Replace(SessionID_Cell, SessionID_Change, Name_Change)
Else
End If

Next SessionID_Cell


End Sub

Answer 1

试试这个:

Sub test()
    Dim idcs As Range, idc As Range
    Dim rng As Range, idns As Variant
    Dim i As Long

    With Sheet1 '~~> Sheet where your data reside, change to suit
        Set idcs = .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
        Set rng = .Range("B2", .Range("B" & .Rows.Count).End(xlUp))
        '~~> pass name id's to array
        idns = Application.Transpose(rng)
    End With

    With CreateObject("Scripting.Dictionary")
        '~~> Dump id's and equivalent city on Dictionary
        For Each idc In idcs
            If Not .Exists(idc.value) Then
                .Add idc.Value, idc.Offset(0, 1).Value
            End If
        Next
        '~~> replace all name id's with equivalent city
        '~~> using the info dumped in the Dictionary
        For i = LBound(idns) To UBound(idns)
            If .Exists(idns(i)) Then idns(i) = .Item(idns(i))
        Next
        '~~> Change this part if you want to dump new data in another sheet
        rng = Application.Transpose(idns)
    End With
End Sub

这会将所有名称标识符替换为实际的城市。
这就像根据 C 和 D 处设置的条件立即查找和替换。
如果标识符没有匹配的城市，它将保持原样。
这就是你正在尝试的吗？