Results 1 to 4 of 4

Thread: application.worksheetfunction.vlookup not working for me

  1. #1

    application.worksheetfunction.vlookup not working for me

    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.

    Code:
    ' 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)

  2. #2
    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.

    Code:
    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)"

  3. #3
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    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.

  4. #4
    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:
    Code:
    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

Similar Threads

  1. Replies: 5
    Last Post: 01-07-2014, 02:00 AM
  2. Replies: 4
    Last Post: 07-10-2013, 04:35 AM
  3. Application.Caller
    By Transformer in forum Familiar with Commands and Formulas
    Replies: 0
    Last Post: 05-01-2013, 12:32 PM
  4. WorksheetFunction.MInverse Method
    By Rasm in forum Excel Help
    Replies: 1
    Last Post: 12-06-2011, 07:55 AM
  5. Application.ScreenUpdating = False not working
    By LalitPandey87 in forum Excel Help
    Replies: 2
    Last Post: 11-08-2011, 08:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •