VLOOKUP Question

[rollingzep]

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.

    '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 = "@"

 

[Legacy 143009]

I didn't quite get your routine. But can it be, you are casting the range here instead of value?

If Not IsError(Chk) Then Data(i, 28) = .Cells(Chk, 4).Value

 

[HongRu]

I run and check your code, and find all work well including the col S part.

Chk = Application.Match("*" & Data(i, 19) & "*", .Columns(1), 0)