rollingzep
Board Regular
- Joined
- Nov 18, 2013
- Messages
- 224
- Office Version
- 365
- Platform
- Windows
Hi,
I am trying to do a Vlookup between two sheets on a same workbook.
WS1, StockRec_Liquidity_Rpt has two columns Q and S which has to lookup col A of the second WS2, BBGRisk and get the values from col D of WS2, BBGRisk to fill values on col AB on WS1, StockRec_Liquidity_Rpt.
This is my code. While it fills up values matching Q of StockRec_Liquidity_Rpt to col A of BBGRisk, it fails to do the same for Col S. It fills up the header of Col D as the value than actual data.
I am trying to do a Vlookup between two sheets on a same workbook.
WS1, StockRec_Liquidity_Rpt has two columns Q and S which has to lookup col A of the second WS2, BBGRisk and get the values from col D of WS2, BBGRisk to fill values on col AB on WS1, StockRec_Liquidity_Rpt.
This is my code. While it fills up values matching Q of StockRec_Liquidity_Rpt to col A of BBGRisk, it fails to do the same for Col S. It fills up the header of Col D as the value than actual data.
VBA Code:
'ws.Range("AB2:AB" & ws.Cells(Rows.Count, "Q").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-11]&""*"",BBGRisk!C1:C4,4,0)"
With Sheets("StockRec_Liquidity_Rpt").Cells(1).CurrentRegion.Resize(, 35)
Data = .Value
With Sheets("BBGRisk").Cells(1).CurrentRegion
For i = 2 To UBound(Data)
Chk = Application.Match(Data(i, 17), .Columns(1), 0)
If IsError(Chk) Then
Chk = Application.Match("*" & Data(i, 19) & "*", .Columns(1), 0)
End If
If Not IsError(Chk) Then Data(i, 28) = .Cells(Chk, 4)
Next i
End With
.Value = Data
End With
ws.Range("AD:AD").NumberFormat = "@"