# VBA to lookup and enter values



## JayB0730 (Dec 25, 2022)

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 CodeData 1FlagABC_Information_*X*DEF_Information_GHI_Information_*X*JKL_Information_*X*MNO_Information_

*Allocations Table*

Transaction CodeData 1Data 2Data 3FlagDEF_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 (Dec 25, 2022)

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 (Dec 25, 2022)

Amazing, thank you! So much to learn


----------

