The following code is part of a macro which I use to check a list of entries against a list of known staff. If the name being checked in the code does not appear in the list, the user is notified and the name is added to the list. The lookup function always returns a value from the column it references, so the code checks to see whether the returned value is equal to the name it is checking for. However, the first entry on the staff list begins with "Ash", and when the code is looking for a value beginning with "Adb", the lookup function will try to return a name before the entry beginning with "Ash". Since there is no value before "Ash", it returns an error. As a temporary solution, I have added someone named "Aardvark" to the beginning of the staff list, but I would prefer to have a more elegant soltion.
Thanks.
If (Application.WorksheetFunction.Lookup((ActiveCell.Offset(0, 0).Range("A1")), Sheets("Temp").Range("A2:A10000"))) <> ActiveCell.Offset(0, 0).Range("A1") Then
What value do you want returned for a non-existant name?
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
I would love it if it just returned a null value. That's easy to work with.
Perhaps simply switch to a simple Match, run @ Application level you can set the result to be a Variant to trap errors without need for double evaluation
Obviously you can use the IsNumeric test to be the precursor for subsequently adding the name to the list or whatever it is you want to do.Dim vResult As Variant vResult = Application.Match(ActiveCell.Value,Sheets("Temp").Range("A2:A10000"),0) If Not IsNumeric(vResult) Then vResult = ""
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Or you could use
The CStr converts the error values to strings so there is no type mismatch error.=IF CStr(Application.Lookup(...)) <> CStr(ActiveCell.Cells(1,1) Then
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks