VBA to lookup and enter values

[JayB0730]

Hello,

I have two worksheets ("TransCode" and "Allocations"), and I would like to look up the values in the "TransCode" table from the first column ("Transaction Code"). Wherever there is a flag ("X"), I want to enter that in the appropriate column in the Allocations worksheet.

TransCode (This list could constantly grow - would like the loop to stop at the last row) :

Transaction Code	Data 1	Flag
ABC	Information	X
DEF	Information
GHI	Information	X
JKL	Information	X
MNO	Information

Allocations Table

Transaction Code	Data 1	Data 2	Data 3	Flag
DEF	Information	Information	Information
JKL	Information	Information	Information	The code will place "X"
ABC	Information	Information	Information	The code will place "X"
MNO	Information	Information	Information
GHI	Information	Information	Information	The code will place "X"

I am trying to create a simple and fast code.

I appreciate any help you can provide.

Best,
J

 

[offthelip]

The realy super fast way of doing that is using the dictionary object in VBa try this code:

Sub dicindexmatch()
   Dim Ary   As Variant
   Dim i As Long
   Dim Dic As Object
   Dim lastrow As Long
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("TransCode")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(lastrow, 3))
   End With
   For i = 2 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 3)                ' Store the X in the dictionary
   Next i
   With Worksheets("Allocations")
      lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
      inarr = .Range(.Cells(1, 1), .Cells(lastrow, 1))
      outarr = .Range(.Cells(1, 5), .Cells(lastrow, 5))
  
      For i = 2 To lastrow
         If Dic.Exists(inarr(i, 1)) Then   ' check if element exists
         outarr(i, 1) = Dic(inarr(i, 1)) ' Copt Dictionary vvalue to output
         Else          ' if it doesn't set to not found
         outarr(i, 1) = "Not Found"
         End If
        
      Next i
      .Range(.Cells(1, 5), .Cells(lastrow, 5)) = outarr  ' write out the output array
    
   End With
End Sub

 

[JayB0730]

Amazing, thank you! So much to learn