Vlook Up not returning any value

[Sg2209]

Dear Friends ,

i am struggling with Vloook as i am new to VBA , i am trying to get the values with the help of Vlook up once workbook gets opened unfortunately not getting any values , its blank not sure what is wrong with the code

i am running a vlook in worksheet Refund Payable report and in column R from another workbook which is Insurance plan in Coumn B to C
here is the normal vlook up

=VLOOKUP(H2,'[Insurance Plans.xlsb]Ins Plan Number'!$B1:$C20000,2,0)

Sub Open_Workbooks()
Dim wb As Workbook
Dim lastrow As Long, i As Long
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("Y:\Sachin Gupta\Cannon\Insurance Plans.xlsb")
Set x = extwbk.Worksheets("Ins Plan Number").Range("B1:C20000")
With twb.Sheets("Refund Payable report")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 18) = Application.VLookup(.Cells(rw, 18).Value2, x, 3, False)
Next rw
End With
extwbk.Close savechanges:=False

i had post this query on another forum too however i was not getting any resolution , re-posting it here , if meanwhile i got response will keep you posted , Shall anyone please review and let me know where i am wrong .
Your help is highly appreicated

 

[GraH - Guido]

Perhaps...

Application.VLookup(.Cells(rw, 18).Value2, x, 3, False)

<>
=VLOOKUP(H2,'[Insurance Plans.xlsb]Ins Plan Number'!$B1:$C20000,2,0)

you do set the range to contain only 2 columns

Set x = extwbk.Worksheets("Ins Plan Number").Range("B1:C20000")

How can vlookup fetch a result from the third column?

 

[Sg2209]

thank you for the response GraH.. it's not fetching up with the 2 column too..i thought may be it counted as ABC the 3 colum is C

 

[NARAYANK991]

Hi ,

It would have helped if you had uploaded the workbook with the data in it.

However , I am unable to understand this :

x is a two column range ; how are you using the column parameter 3 in the code , and saying that it returns a blank ?

Application.VLookup(.Cells(rw, 18).Value2, x, 3, False)

I tried out your code , and I get error values , not blanks.

Narayan

 

[Sg2209]

thanks Narayank991n. i would upload both the files . i did try with 2 also . as i am very new in VBA i i changed it to 3 thought of C is then3 column.

 

[Sg2209]

GraH - Guido i just try to use this Application.VLookup(.Cells(rw, 18).Value2, x, 2, False) however it says comile error expected := , Would you please review .

i would be uploading this files tonight

 

[Sg2209]

thank You everyone , i have read my code again and got the resolution myself 10 lines needs to be amended to Application.VLookup(.Cells(rw, 8 instead 18 and i got my results , please see the full code updated code

Dim wb As Workbook
Dim lastrow As Long, i As Long
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("Y:\Sachin Gupta\Byram\Insurance Plans.xlsb")
Set x = extwbk.Worksheets("Ins Plan Number").Range("B:C")
With twb.Sheets("Refund Payable report")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 18) = Application.VLookup(.Cells(rw, 8).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False

 

[Sg2209]

Now again i am coming to one more scenario now how can remove #NA from the above code , i mean how do i use iferror ? any help please

 

[GraH - Guido]

How would you do it in Excel? Wrap the iferror around the vlookup works in VBA.

 

[Sg2209]

i tried but its not working

 

[Marc L]

Hi !

First try manually a worksheet formula and,
once correct, post here this working formula …
​

 

[Sg2209]

Worksheets("Refund Payable report").Range("A1:AZ1").Select
Columns("R:U").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I did it for my entire sheet, working, thank you