Vlookup Column in another sheet

[cssfonseca]

Hello.
I have a Table where i have values in Column A. And i want to lookup dat value in sheet2, and replace it and the same column. All of this with the trigger of a button.

I have stumbled around many codes, but none of them seems to fit my purpose.

Explained
[TABLE="width: 500"]
<tbody>[TR]
[TD]Button Trigger[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]name_category[/TD]
[TD]product_id[/TD]
[/TR]
[TR]
[TD]Television[/TD]
[TD]233431[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]2334[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]code[/TD]
[TD]name of category[/TD]
[/TR]
[TR]
[TD]TEL01[/TD]
[TD]Television[/TD]
[/TR]
[TR]
[TD]COM01[/TD]
[TD]Computer[/TD]
[/TR]
</tbody>[/TABLE]


So I want the value of the name of category to be replaced for the code, in sheet1, on the click of the button. My main problem is how to define the range, since i dont have a fixed size to the columnA.

Can somebody help me with this code?

 

[alansidman]

Option Explicit


Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr1 As Long, lr2 As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    For i = 2 To lr1
        For j = 2 To lr2
            If s2.Range("B" & j) = s1.Range("A" & i) Then
                s1.Range("A" & i) = s2.Range("A" & j)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

 

[Fluff]

Another option

Sub replacecat2()
   Dim cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("Pcode")
   
   With CreateObject("scripting.dictionary")
      For Each cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
         .Item(cl.Value) = cl.Offset(, -1).Value
      Next cl
      For Each cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         cl.Value = .Item(cl.Value)
      Next cl
   End With
End Sub

 

[cssfonseca]

It worked perfectly! i just had to replace the columns for the one i wanted. Easiest lookup ever. Thank you for the fast reply

 

[Fluff]

Not sure who you're talking too, but glad we could help & thanks for the feedback

 

[cssfonseca]

Option Explicit


Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr1 As Long, lr2 As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    For i = 2 To lr1
        For j = 2 To lr2
            If s2.Range("B" & j) = s1.Range("A" & i) Then
                s1.Range("A" & i) = s2.Range("A" & j)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

So its running perfect, except when i look for more than one hundred values. Sheet 3 has over 100.000 values. Should i give up for its to heavy for my pc?

 

[cssfonseca]

Another option

Sub replacecat2()
   Dim cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("Pcode")
   
   With CreateObject("scripting.dictionary")
      For Each cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
         .Item(cl.Value) = cl.Offset(, -1).Value
      Next cl
      For Each cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         cl.Value = .Item(cl.Value)
      Next cl
   End With
End Sub

.Item(cl.Value) = cl.Offset(, -1).Value
i have an error on this line of code.
[h=1]Application-defined or object-defined error. Where did i went wrong?[/h]

 

[Fluff]

What columns are the Code & category in on sheet2?

 

[cssfonseca]

In sheet 2 the code is in A and the cat. in B. But in sheet 1 the info is in D. Perhaps i swapped the vars.

 

[Fluff]

Ok, how about

Sub replacecat2()
   Dim cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("Sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
         .Item(cl.Value) = cl.Offset(, -1).Value
      Next cl
      For Each cl In Ws1.Range("D2", Ws1.Range("D" & Rows.Count).End(xlUp))
         cl.Value = .Item(cl.Value)
      Next cl
   End With
End Sub