Application.WorksheetFunctions

[AMDRIT]

Hello,

I am recieving Error 2015 when I call VLookUp, anyone have any ideas why?
TIA

Lookup Table Named Range "TestLookUp"

Test Lookup
Key Value
1 Value1
2 Value2
3 Value3
4 Value4

private mChanging as boolean

Private Sub Worksheet_Change(ByVal Target As Range)

If mChanging then exit sub
If Target.Address <> "$B$1" then exit sub

mChanging = true

Dim sNamedRange as string
sNamedRange = replace(ActiveWorkbook.Names("TestLookUp").RefersTo,"=","")
If Not Application.IsError(Application.VLookup(Target.Value,
sNamedRange, 2, False)) Then
If Not
Application.WorksheetFunction.IsNA(Application.VLookup(Target.Value,
sNamedRange, 2)) Then
Target.Worksheet.Range("$A$1").Formula = "VLOOKUP(""" &
target.address & """,""TestLookUp"",2)"
Else
Target.Worksheet.Range("$A$1").Formula = ""
Target.Worksheet.Range("$A$1").Value = ""
End If
End If

mChanging = false

End Sub

 

[AMDRIT]

This seems to work

Application.VLookup(cint(target.value),
ActiveWorkbook.ActiveSheet.range("TestLookUp"), 2,false)

 

[Bob Phillips]

Vlookup expects the lookup table as arange, not a string, so maybe

Application.VLookup(Target.Value, Range(sNamedRange), 2, False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[AMDRIT]

This works better:

dim result as variant

result = Application.VLookup(CInt(Target.Value),
ActiveWorkbook.Names("TestLookUp").RefersToRange, 2, False)

If Not Application.IsError(result) And Not Application.IsNA(result) Then
Target.Worksheet.Range("$A$1").Formula = "=VLOOKUP(""" & target.address &
""",TestLookUp,2)"
Else
Target.Worksheet.Range("$A$1").Clear
End If

 

[AMDRIT]

You were correct, thanks a lot.

Vlookup expects the lookup table as arange, not a string, so maybe

Application.VLookup(Target.Value, Range(sNamedRange), 2, False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)