Excel 2013 VBA Vlookup-Function Error

0
votes

I wrote a program, which creates a resource-Overview. At a point in the program i want to set a vlookup-function via VBA. If I set the vlookup-function manually in the sheet (write the formula into the cell)(=SVERWEIS(B2;'MS Project'!A:H;2;FALSCH))("SVERWEIS" is the german word for "VLOOKUP", "FALSCH" is the german word for "FALSE") it works fine, but if I'm use the following line of code to insert this Formula automatically into the cell, it does not.

rngTarget.Cells(i, col + 1).value = WorksheetFunction.VLookup(rngTarget.Range("B" & i), _
ThisWorkbook.Worksheets("MS Project").Range("A1:H2000"), 2, 0)

"rngTarget" represents the usedRange of the activeSheet.

"i" represents the current row I'm working with.

The Image below shows the runtime-error (1004) I get while executing my program.

enter image description here

It says, that the Vlookup-Property of the WorksheetFunction-Object could not be assigned.

Edit: before this line gets executed, I check if the searched object exist in "Ms Project". Only if it does exist, it will set the vlookup-function.

SOLUTION: I figured out the problem, I changed:

rngTarget.Cells(i, col + 1).value = WorksheetFunction.VLookup(rngTarget.Range("B" & i), _
ThisWorkbook.Worksheets("MS Project").Range("A1:H2000"), 2, 0)

to:

rngTarget.Cells(i, col + 1).value = Application.VLookup(rngTarget.Range("B" & i).value, _
ThisWorkbook.Worksheets("MS Project").Range("A1:H2000"), 2, 0)
1
vbaexcelvlookup

1 Answers

0
votes

For the Lookup_value argument you are referencing the whole object rngTarget.Range("B" & i), try adding .Value2 to return only the value for this range.

i.e. 

rngTarget.Cells(i, col + 1).value = WorksheetFunction.VLookup(rngTarget.Range("B" & i).Value2, _
ThisWorkbook.Worksheets("MS Project").Range("A1:H2000"), 2, 0)