| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel.worksheetfunctions | Excel Worksheet Functions | |||
| WorksheetFunctions | Excel Programming | |||
| can I use WorksheetFunctions ON arrays from VBA?? | Excel Programming | |||
| No VBA help for Worksheetfunctions available | Excel Worksheet Functions | |||
| Using WorksheetFunctions in Excel Macros | Excel Programming | |||
Linear Mode
