Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Virum, Denmark
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post Problem with VLookup and error 2042

    Hi, I am trying to make a macro that lookup one value from the active sheet and returns the value from the lookuptable

    all I get is #N/A

    can anyone help ?

    the code looks like this :

    Sub midtest()

    Dim LResult As String
    Dim Result As Variant
    Dim Dependents As Range

    Set Dependents = Workbooks("Varermedeankode.xlsx").Worksheets("Shee t1").Range("Intern_tekst")

    LResult = Val(Mid(Range("A14").Value, 8, 13))
    'LResult = Mid(Selection.Formula.Value, 8, 13)

    'Result = Application.VLookup(LResult, Dependents.Value, 2, False)
    'Result = Application.WorksheetFunction.VLookup(LResult, Dependents.Value, 2, False)
    Result = Application.Evaluate(Application.VLookup(LResult, Dependents.Value, 2, False))

    Range("c14") = Result

    End Sub

    kind regards

    Peter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Could you possibly post the workbook?

    You might also try dropping the .value from the VLookup search range parameter.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Virum, Denmark
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post hi retiredgeek

    i did not expect an answer so quickly - forgot the time difference.

    the files are now attached.

    i have tried everything - same result.

    also i would like to make the lookup even when the lookupfile is closed - is that possible ?

    regards
    Peter
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Jensen,

    I am not sure if this is what you are trying to achieve by looking at your code.

    HTH,
    Maud

    Code:
    Sub midtest()
    Dim LResult As String
    Dim Result As Variant
    Dim Dependents As Range, cell As Range
    With Workbooks("Varermedeankode.xlsx").Worksheets("Sheet1")
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Dependents = .Range("A2:A" & LastRow)
        LResult = Mid(.Range("A14"), 8, 13)
        For Each cell In Dependents
            If InStr(1, cell, LResult, vbTextCompare) Then
                Result = cell.Offset(0, 2)
            End If
        Next cell
        .Range("J14") = Result
    End With
    End Sub
    Note: I returned the result to J14 instead of C14 as not to overwrite anything
    Last edited by Maudibe; 2015-06-27 at 10:28. Reason: added note

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Here's your code fixed. It was a problem w/variable typing.
    Code:
    Sub midtest2()
    
    Dim dResult    As Double
    Dim zResult    As String
    Dim Dependents As Range
    
       Set Dependents = Workbooks("Varermedeankode.xlsx").Worksheets("Sheet1").Range("Intern_tekst")
    
       dResult = Val(Mid(Range("A14").Value, 8, 13))
    
       zResult = Application.VLookup(dResult, Dependents.Value, 2, False)
    
       Range("c14") = zResult
    
    End Sub
    You can not do this with the lookup workbook closed. You can however trap the error when you try to reference the lookup workbook and it is not open and then in the error processing open the workbook. I'll generate this code if you would like.

    BTW: If you still get an error try deleting all the #Name errors in the lookup table. I did this while testing for the problem and did not reintroduce them when I found a solution so they may be causing some of the problem.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    xxxjensen (2015-06-29)

  7. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Virum, Denmark
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi Retiredgeek,

    that worked fine, thank you very much.

    regards Peter

  8. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Virum, Denmark
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi Maud

    this works, but it is the wrong workbook - the result should be in the testfile.

    thanks
    Peter

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
  •