How to apply vlookup on the different workbook range?

0
votes

I am trying to apply VLOOKUP to get the data from a workbook named LPDwith sheet RAS(Offshore) having my lookup range. I want to look for the column A which is present in a different workbook named fresher with sheet as DestSh.So I want the results after applying VLOOKUP in the sheet DestSh in column z.

I have tried

iSCount = 2
For Each cell In DestSh.Range("Z2:Z" & lstRowofIpSheet)   
    With DestSh
        sFormula = "=VLOOKUP(DestSh!$A" & iSCount & ",'" & sLPDFileName & "]RAS(Offshore)'!$B:$F,5,false)"    
        .Range("Z" & iSCount).Formula = sFormula
        iSCount = iSCount + 1
    End With
Next cell

But I am getting the error as: Application defined or Object defined error.

Any help or suggestions shall be highly appreciated.

1
vbaexcel
You're missing the opening [. – BruceWayne

1 Answers

2
votes

In this line:

sFormula = "=VLOOKUP(DestSh!$A" & iSCount & _
            ",'" & sLPDFileName & "]RAS(Offshore)'!$B:$F,5,false)"

Missing an opening square bracket:

sFormula = "=VLOOKUP(DestSh!$A" & iSCount & _
            ",'[" & sLPDFileName & "]RAS(Offshore)'!$B:$F,5,false)"