VLookup rather than Loop

[rollingzep]

Hi,

<p>
Currently, I use For, Next loops to match and return values from two worksheets and populate col BC of WS1.
The values of Col AG in WS1 have 9 chars and on WS2 there are 2 cols, Col A has 9 chars and Col B has 12 chars.

For example, the value in Col AG of WS1,912828YG9 has no match on Col A of WS2 but has a match on Col B as US912828YG91

Currently, I use the below code which works perfectly

</code>
With Sheets("TSS Trans").Cells(1).CurrentRegion.Resize(, 55)
Data = .Value
With Sheets("HQLA_Pools").Cells(1).CurrentRegion
For i = 2 To UBound(Data)
Chk = Application.Match(Data(i, 33), .Columns(1), 0)
If IsError(Chk) Then
Chk = Application.Match("*" & Data(i, 33) & "*", .Columns(2), 0)
End If
If Not IsError(Chk) Then Data(i, 55) = .Cells(Chk, 3)
Next i
End With
.Value = Data
End With

ws.Range("BC:BC").NumberFormat = "@"
</code>

But since the data volume is large and I re-use this code for two other WS, the entire process consumes close to 8-10 mins.

Can this be done using VLookup using an IF condition?

I am not sure how to use the second column here, using the IF condition. The below VLookup matches one Col in WS2.

<code>
'ws.Range("BC2:BC" & ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C1:C3,3,0)"
</code>

</p>

Thanks

 

[offthelip]

You are half way there in terms of speeding this up since you load the some of data into a variant array ( Data) , however doing a match on three columns of data for every row in data is very slow. There is much faster way which is to load the data in HQLA POOL into a three dictionaries and then you can use those directly in vba to do the looup. This saves thousandes of accesses to hte worksheet and so the macro should take seconds not minutes, try this:

   Dim Dic1 As Object
   Set Dic1 = CreateObject("Scripting.dictionary")
   Dim Dic2 As Object
   Set Dic2 = CreateObject("Scripting.dictionary")

With Sheets("TSS Trans").Cells(1).CurrentRegion.Resize(, 55)
Data = .Value
With Sheets("HQLA_Pools").Cells(1).CurrentRegion
Ary = .Value
   For i = 1 To UBound(Ary)
      Dic1(Ary(i, 1)) = Ary(i, 3)
      Dic2(Ary(i, 2)) = Ary(i, 3)

   Next i
For i = 2 To UBound(Data)
  If Dic1.Exists(Data(i, 33)) Then
     Data(i, 55) = Dic1(Data(i, 33))
   End If
  If Dic2Exists(Data(i, 33)) Then
     Data(i, 55) = Dic2(Data(i, 33))
   End If

End With
.Value = Data
End With