Problem with VLookup and error 2042

Topic

New Reply

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(“Sheet1”).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

Reply | Quote

Replies

Peter,

Could you possibly post the workbook?

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

hi Maud

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

thanks
Peter

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

hi Retiredgeek,

that worked fine, thank you very much.

regards Peter

Reply | Quote