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
899
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
899
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,268
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,268
Office Version
365
Platform
Windows
Glad we could help - welcome to the forum btw :)
 

Forum statistics

Threads
1,085,294
Messages
5,382,769
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top