Vlook Up not returning any Values

[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

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

Shall anyone please help also instead of .Range("B1:C20000") am i able to use .Range("B1:B" & Ilastrow)

 

[sg2209]

here is the normal vlook up

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

 

[sg2209]

Any chance here please

here is the normal vlook up

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

 

[FormR]

here is the normal vlook up

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

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

Hi, can you spot the difference?

Shall anyone please help also instead of .Range("B1:C20000") am i able to use .Range("B1:B" & Ilastrow)

I think that you could use full column references with very little impact on performance, i.e.

Set x = extwbk.Worksheets("Ins Plan Number").Range("B:C")

 

[sg2209]

i did try with it and also i changed
.Cells(rw, 18) = Application.VLookup(.Cells(rw, 18).Value2, x, 2 False)

i am not sure why is this running blank

 

[FormR]

Hi, can you post your updated code in full?

 

[sg2209]

this is my full code... am i missing something here

 

[FormR]

this is my full code... am i missing something here

Hi, the code in post 1 is trying to return data from the 3rd column of a 2 column lookup table, in post 5 you imply that you have since changed it - can we see that changed code in full, copied directly from your workbook?

 

[sg2209]

Please see below it not working too.

[Dim wb As WorkbookDim 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, 18).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False

 

[FormR]

Please see below it not working too.

What happens if you step through the code from the VBE using F8?

i am trying to get the values with the help of Vlook up once workbook gets opened

Are you trying to have this happen automatically when you open the workbook that contains the code? If so, the sub must be called:

Private Sub Workbook_Open()

And it must be in the "ThisWorkbook" module.