更新Vlookup中的唯一值 [英] Updating unique values in Vlookup

问题描述

我有以下代码，使用UG列表作为源，并在两个不同的表 - 延迟和TT上执行vlookup。

I have the below code, that uses "UG list" as the source and does vlookup on two different sheets - Latency and TT.

如果找到结果，它会将字符串UG传递到每个工作表的特定列。

If the result is found it passes the string "UG" onto each sheet's specific column.

问题即使有重复的值字符串UG得到更新...但是我想要的是，UG应该被更新为唯一的值。不应该为同一个值一次更新。

The problem is even if there are duplicate values the string "UG" gets updated..But what I want is, "UG" should be updated for unique value..it should not be updated for the same value again and again.

Sub vlookup()
Dim cl As Range, Dic As Object
Set Dic = CreateObject("Scripting.Dictionary"): Dic.Comparemode = vbTextCompare
With Sheets("Latency")
For Each cl In .Range("B2:B" & .Cells(Rows.count, "C").End(xlUp).Row)
    If Not Dic.exists(cl.Value) Then Dic.Add cl.Value, cl.Row
Next cl
End With
With Sheets("UG list")
For Each cl In .Range("A2:A" & .Cells(Rows.count, "A").End(xlUp).Row)
    If Dic.exists(cl.Value) Then
        Sheets("Latency").Cells(Dic(cl.Value), 17) = "UG"
    End If
Next cl
End With

With Sheets("TT")
For Each cl In .Range("A2:A" & .Cells(Rows.count, "C").End(xlUp).Row)
    If Not Dic.exists(cl.Value) Then Dic.Add cl.Value, cl.Row
Next cl
End With
With Sheets("UG list")
For Each cl In .Range("A2:A" & .Cells(Rows.count, "A").End(xlUp).Row)
    If Dic.exists(cl.Value) Then
        Sheets("TT").Cells(Dic(cl.Value), 23) = "UG"
    End If
Next cl
End With

Set Dic = Nothing
End Sub

推荐答案

两件事：

1. 您正在为不同的表使用相同的字典 Dic 。在将其用于下一页之前，请清除该字典，以使您没有任何旧值。

1. You are using the same dictionary Dic for different sheets. Before using it for next sheet, clear the dictionary, so that you dont have any old values.

dic.RemoveAll
带表格（TT）
.........

dic.RemoveAll With Sheets("TT") .........

---

---

1. 为了防止第二次更新，只要您在第一次找到该项时，只需从字典中删除该项。虽然我不知道你所指的是什么重复的值，但是在字典中你不能有重复的。

1. To prevent the second updates, just remove the item from dictionary, as soon as as you find it first time. Though I am not sure what duplicate value you are referring to, as in dictionary you can't have duplicates.

If dic.exists(cl.Value) Then
    Sheets("Latency").Cells(dic(cl.Value), 17) = "UG"
    dic.Remove (cl.Value)
End If

---

如果您只是在谈论场景，如果列Q已经有UG，并且您想跳过该单元格，那么只需手动检查。

---

If you are just talking about the scenario where if the Column Q already has got "UG" in it and you want to skip that cell then just check it before hand.

  If dic.exists(cl.Value) Then
        If Sheets("Latency").Cells(dic(cl.Value), 17) <> "UG" Then
            Sheets("Latency").Cells(dic(cl.Value), 17) = "UG"
        End If
    End If

这篇关于更新Vlookup中的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！