VBA Vlookup to update the same row value

I have a vlookup code that will update the text “UG” in a destination column if the result matches with the source sheet.

Example :The code compares “Sheet B” with “Sheet A” and if a match is found in “Sheet A” , it updates the text “UG” in a column in sheet A.

I want to make a small modification here.

Instead of updating the text “UG” in sheet A, I want to update the value that is there in the “Sheet B” in col B .

Sub UGvlookup()
Dim cl As Range, Dic As Object
Set Dic = CreateObject("Scripting.Dictionary"): Dic.Comparemode = vbTextCompare
With Sheets("Sheet A")
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("Sheet B")
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
If Dic.exists(cl.Value) Then
Sheets("Sheet A").Cells(Dic(cl.Value), 17) = "UG"
Dic.Remove (cl.Value)
End If
Next cl
End With


Top Contributor Asked on January 19, 2017 in VBA: Programming.
Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.