Application.WorksheetFunctions
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" wrote in message
...
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").RefersT o,"=","")
If Not Application.IsError(Application.VLookup(Target.Val ue,
sNamedRange, 2, False)) Then
If Not
Application.WorksheetFunction.IsNA(Application.VLo okup(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
|