Consecutive lookup gives errors

[rollin]

Hi,

I use a lookup function to fill values on a worksheet, SecOS using values from a 2nd worksheet, HQLA_Equity. It works fine.

On the same worksheet,SecOS , I use another lookup using values from a 3rd worksheet, BRisk.
When the code executes this, it alters the values filled from first lookup

<code>

    Set wbexcel = ThisWorkbook

    Set ws = wbexcel.Sheets("SecOS")

    ws.Cells.ClearContents

   

 

'    strfilename = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")

 

    strfilename = Application.GetOpenFilename

    With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A1"))

        .TextFileParseType = xlDelimited

        .TextFileCommaDelimiter = True

        .Refresh

 

    End With

   

    MsgBox ("SecOS file imported")

   

    Set wbexcel = ActiveWorkbook

    Set ws = wbexcel.Sheets("SecOS")

   

    

   

    'Add HQLA_FR2052A column

    

    ws.Range("AS1").Value = "HQLA_FR2052A "

   

   'Add HQLA_FR2052A values

   

    ws.Range("AS2:AS" & ws.Cells(Rows.Count, "V").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-23]&""*"",HQLA_Equity!C3:C29,27,0)"

   

    

    ' Add SECTOR_DES column

   

    ws.Range("AT1").Value = " SECTOR_DES"

   

 

    'Read SECTOR_DES values from BRisk sheet and fill col AT in SecOS

 

'    Set ws = wbexcel.Sheets("SecOS")

     

'   Populate column AT with formula

 

'    ws.Range("AT2:AS" & ws.Cells(Rows.Count, "V").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",BRisk!C1:C4,4,0)"

</code>