|
Application.WorksheetFunctions
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 |
Application.WorksheetFunctions
This seems to work
Application.VLookup(cint(target.value), ActiveWorkbook.ActiveSheet.range("TestLookUp"), 2,false) "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 |
Application.WorksheetFunctions
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" 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 |
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 |
Application.WorksheetFunctions
You were correct, thanks a lot.
"Bob Phillips" wrote in message ... 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" 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 |
| All times are GMT +1. The time now is 07:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com