Custom function to replace several vlookups

AB1984

New Member
Joined
May 12, 2016
Messages
17
I'm trying to create a UDF / custom function that takes the place of four vlookups to return a boolean response. It seems to work fine when I'm debugging it (I have a separate sub which calls the function so I can step through it), but when I attempt to use it, it does appear in the autocomplete box, but I always get the #REF error. I know this is meant to mean to mean that one of the references isn't there, but I really can't see what's wrong.
I've checked and double checked the values for the table array it's referencing so I know those values are correct. If someone could take a look at point out where I've gone wrong it would be greatly appreciated!

Public Function CYP102(CellRef As Range) As Boolean
Dim C102 As String
C102 = Application.WorksheetFunction.VLookup(CellRef, Sheets("Perms").Range("A2:I3001"), 7, 0)
If C102 = "16" Or C102 = "28" Or C102 = "33" Or C102 = "44" Then
CYP102 = True
Else
CYP102 = False
End If
End Function
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
890
I cant see what's wrong with this to be honest (im not great with excel functions!).

However, try using FALSE instead of 0.

Code:
C102 = Application.WorksheetFunction.VLookup(CellRef, Sheets("Perms").Range("A2:I3001"), 7, FALSE)
This is what is used in all the vba examples i've seen.

Regards
Caleeco
 

AB1984

New Member
Joined
May 12, 2016
Messages
17
Hi,
thanks for your input there, I tried the change, but it had no effect on my function, grrr :(

cheers!
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
890
Hello,

Ok thanks for letting me know. Hav eyou check that the correct data is passed to the function? i.e. 'CellRef As Range' is indeed a range (not a string).

How is the function called from the main SUB?

Regards
Caleeco
 

AB1984

New Member
Joined
May 12, 2016
Messages
17
Hi Caleeco,

It should be a cell reference. I want to be able to use the function (when it's finished) is the form:
=CYP102(A34)
for example.

I'm calling it in my testing sub as

Code:
Sub macro()
MsgBox CYP102(Cells(34, 1))
End Sub
which I'm hoping should return the same value that would appear in the cell if the function was successful
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Hi, if you are using the function in the worksheet then you need to name it something other than CYP102().

CYP102 is a cell address and this confuses Excel.
 

AB1984

New Member
Joined
May 12, 2016
Messages
17
Ahhh, we're getting Somewhere now. Thanks for the tip!
Unfortunately now, even though I've got rid of the #REF! error, the function is only returning 0. I'm not sure if that's the value zero, or it means false. Either way, it doesn't change when the test is true.
 

AB1984

New Member
Joined
May 12, 2016
Messages
17
Fixed it!
I had another variable in there that could've been a cell reference. All looking good now:
Code:
Public Function MVlooks(CellRef As Range) As Boolean
    Dim MVLU As String
     MVLU = Application.WorksheetFunction.VLookup(CellRef, Sheets("Perms").Range("$A$2:$I$3001"), 7, False)
     If MVLU = "16" Or MVLU = "28" Or MVLU = "33" Or MVLU = "44" Then
        MVlooks = True
     Else
        MVlooks = False
     End If
End Function
Thank you both for you help!
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Glad we could help - welcome to the forum btw :)
 

Forum statistics

Threads
1,082,167
Messages
5,363,528
Members
400,747
Latest member
monty_gl

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top