application.worksheetfunction.vlookup not working for me

[xander1981]

Hello,

I have done this from code file before but with form values and had no problem. Now I need to perform a vlookup between 2 workbooks in code but cannot get the result to come over. please can some lovely person try to help. many thanks in advance.



' Set RANGE TO LOOK AT AS RANGE --------------
Dim oRangeToCheck As Range
ActiveWorkbook.Sheets("Weekly Shipped Details").Select
Set oRangeToCheck = Range("$I$5:$I$1000")
'------------------------------------------------
'Set the cell where the data will be returned to-------------
Workbooks("Shipment Report.xls").Activate
Dim oResult As Range
Set oResult = ActiveSheet.Range("$J2").Value
'------------------------------------------------------------
' SET STRING VARIABLE AS CONTAINER WE WANT TO CHECK -----------
Dim oCheck As String
oCheck = ActiveSheet.Range("I2").Value
'-------------------------------------------------------------
oResult = Application.WorksheetFunction.VLookup(oCheck, oRangeToCheck, 1, 0)

[xander1981]

Further to the above, I think it would be easier to insert the formula rather than a dynamic vlookup because I need to put the result in a number of cells so would be better to get the formula then drap down to last row. so, I have the following code that does this but I need to change it as the workbook in the table array will change year to year so next year when I run this code the table array will be in the workbook with the same name but ending 2015.



what I have

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Shipment Report 2014.xls]Weekly Shipped Details'!R5C9:R1000C9,1,0)"

I need something like

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[""Shipment Report"" & Year(Date) & "".xls]""Weekly Shipped Details'!R5C9:R1000C9,1,0)"

[bakerman]

Can you post 2 workbooks so we can verify the code.
If the purpose is to check for occurence in targetfile VBA has built-in functions to search for a value so you don't need to use worksheetfunctions.

[xander1981]

Thanks Bakerman, This post related to my other thread regarding the loop, I tried to perform a vlookup as above but then decided to do a workbookfunction.vlookup within loop:


On Error Resume Next
Dim result As Variant
For Each oCell In Range(Range("A2"), Range("A1000").End(xlUp))
result = Application.VLookup(oCell, Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details") _
.Range("I5:I1000").Cells, 1, 0)
oCell.Offset(0, 2).Select
Selection.Value = result
Next