0

I need to write vlook up code in VBA for value in Sheet "Page1_1" and fill G column with value from "Sheet1" column "R" 17. So far I have below but its giving:

unable to get the v lookup property of the work sheet function class: 

Function vlookupVBA()
vlookupVBA = "#N/A"
    Set ws = Sheets("Page1_1")
Set sh = Sheets("Sheet1")
    LastRow = ws.Cells(Rows.Count, "G").End(xlUp).Row
    Set TargetRange = sh.Range("R2:G" & LastRow)
On Error Resume Next
  result = Application.WorksheetFunction.VLookup(Worksheets("Page1_1").Range("G2"), TargetRange, 17, False)

 End Function
Dale K
  • 25,246
  • 15
  • 42
  • 71
user11788532
  • 25
  • 4
  • 1
    Possible duplicate of [How to error handle 1004 Error with WorksheetFunction.VLookup?](https://stackoverflow.com/questions/18063214/how-to-error-handle-1004-error-with-worksheetfunction-vlookup) – Kate Orlova Jul 15 '19 at 22:53
  • Have not tried your example because I'm on the road but I can tell you that `Rows.Count` is not qualified so `LastRow` may have the wrong number causing `TargetRange` to be a range that `vLookup` can't handle. For example, add an empty worksheet to your workbook (assuming Sheet 3 ) add the line `Sheet3.Activate` as the first line of the function. In the line immediately after setting `TargetRange` add `MsgBox TargetRange.Address` and you may be surprised at the result. Always, always, always qualify your ranges. Also don't see how`result` is declared, are you using`Option Explicit`? – ProfoundlyOblivious Jul 16 '19 at 02:40
  • Thank you for your reply. I tried your suggestions but it did not work. Regarding 'result', no I am not using Option Explicit. I Dim result as variant. – user11788532 Jul 16 '19 at 15:18

1 Answers1

0

The problem is your column reference number. You ask vlookup to fetch data from column 17 but your TargetRange doesn't have 17 columns.

TargetRange = sh.Range("R2:G" & LastRow)

From column G to column R there are 12 columns. I'm assuming you wanted to fetch your answer from column Q, which would be the 11th column of your range. Hence

result = Application.WorksheetFunction.VLookup(Worksheets("Page1_1").Range("G2"), TargetRange, 11, False)

should work.

ps. You could also rewrite your TargetRange in a more intuitive way:

TargetRange = sh.Range("G2:R" & LastRow)

pps. I encourage you to use the Index/Match combination instead: Excel - VLOOKUP vs. INDEX/MATCH - Which is better?

Pomul
  • 392
  • 3
  • 11