Custom function to replace several vlookups

[AB1984]

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]

I cant see what's wrong with this to be honest (im not great with excel functions!).

However, try using FALSE instead of 0.

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]

Hi,
thanks for your input there, I tried the change, but it had no effect on my function, grrr

cheers!

 

[Caleeco]

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]

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

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]

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]

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]

Fixed it!
I had another variable in there that could've been a cell reference. All looking good now:

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]

Glad we could help - welcome to the forum btw