vlookup vba code autofill not working

Question

Vlookup code filling same value. How to get right values

Source is

ADSU01A 15.14166667
ADSU01B 13.41944444
ADSU01C 12.21111111
ADSU01D 8.64

Vlookup value showing

ADSU01A 15.14166667
ADSU01B 15.14166667
ADSU01C 15.14166667
ADSU01D 15.14166667

Code used is:

lastrow = Sheet1.Range("B" & Rows.Count).End(xlUp).row
Set Mytable = Range("A:G")

For i = 2 To lastrow
    Cells(i, 12) = Application.WorksheetFunction.VLookup(Cells(i, 1), _
                   Range("Mytable"), 3, False)
Next i

Please do not change the edit unless you want to enhance the formatting or add any useful info. — Siddharth Rout, Jun 09 '15 at 10:10
Load it on any file uploding site and share the link here. One of us will upload it for you :) — Siddharth Rout, Jun 09 '15 at 10:20
are you sure that `Range("Mytable")` covers entire range where your data is? Shouldn't you use 2nd column in formula? — Kazimierz Jawor, Jun 09 '15 at 10:23
Hello, i managed to run your code without any problem with some minor corrections. Instead of `Range("Mytable")` , i used onlt `Mytable` since it is already identified as range, then i replaced 3 with 2 in `Cells(i, 12) = Application.WorksheetFunction.VLookup(Cells(i, 1), Range("Mytable"), 3, False)` since it was like that in my sheet. Then code worked just fine. — Dubison, Jun 09 '15 at 10:28
Image linkhttp://expirebox.com/download/da494fa2e66f00025afe7c2d0893ae17.html — Abhas Tiwari, Jun 09 '15 at 10:33
@Dubison..Please check in your sheet, if you put the values of A column, into K column in jumbled sequence...and then take K column as reference..then run code — Abhas Tiwari, Jun 09 '15 at 11:20
yes it works perfectly. All i need to do is to change the `VLookup(ws.Cells(i, 1)` to `VLookup(ws.Cells(i, 11)` where 11 refering the column K. I have updated the code below accordingly. — Dubison, Jun 09 '15 at 11:31

Dubison · Accepted Answer · 2015-06-09T11:35:12.590

0

below code worked fine for me with the layout in the image.

enter image description here

Sub vlookupadd()
Dim WB As Workbook
Dim ws As Worksheet

Set WB = ActiveWorkbook
Set ws = WB.ActiveSheet


lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row
Set mytable = ws.Range("A:G")

For i = 2 To lastrow
    ws.Cells(i, 12) = Application.WorksheetFunction.VLookup(ws.Cells(i, 11), mytable, 2, False)
Next i

End Sub

edited Jun 09 '15 at 11:35

answered Jun 09 '15 at 10:30

Dubison

750
5
12

When i changed as suggested, (i,1) replace by (i,11), I am getting error "Unable to get vlookup property of the worksheetfunction class" , Trying to debug – Abhas Tiwari Jun 09 '15 at 12:56
Hi Abhas, can you please have a look at this topic. They were discussing the same condition. It is mentioned that error may cause because the data you are looking for is not in your list. [LINK](http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error) – Dubison Jun 10 '15 at 11:12

vlookup vba code autofill not working

1 Answers1