A value used in the formula is of wrong data type when calling a UDF

Question

I've been trying hard to figure out what went wrong here. I have two cols which contain string values. I use the third col to call a UDF in the worksheet, but end up getting #Value with error - "A value used in the formula is of wrong data type".

Eg:

 Col I   Col J
 File1    Y
 File1    N
 File2    Y
 File3    N

I tried debugging it, and the input values were passed fine into the function variables. My knowledge in VBA is limited, hoping you guys can help me out in resolving this issue.

Function called in worksheet:

=FileCheck(I3,I3:J38)

Code:

Public Function FileCheck(V As Range, Q As Range) As String
    Dim vtest As Variant
    Dim i, j, stat As Integer

    stat = 0

    vtest = Q

    For i = 1 To UBound(vtest)
        If V = vtest(i, 1) And vtest(i, 2) = "N" Then

            For j = 1 To UBound(vtest)
                If V = vtest(j, 1) And vtest(j, 2) = "Y" Then

                    FileCheck = "Y"
                    stat = 1
                End If
            Next
            If stat = 0 Then
                FileCheck = "N"
                End
            Else: End
            End If


        ElseIf V = vtest(i, 1) And vtest(i, 2) = "Y" Then
            FileCheck = "Y"
        End If
    Next

End Function

Can you please step through your code and provide the line that causes the error? — glh, Jan 22 '13 at 14:16
FYI: on this line `Dim i, j, stat As Integer`, you are only defining `stat` as an integer. `i` and `j` will be defined as variant as there is no AS statement before the comma separating them — SeanC, Jan 22 '13 at 14:34
Thanks for this, I had taken this as easy route of declaring variables without realising this would invariably declare them as Variants. — user2000380, Jan 23 '13 at 03:18

Siddharth Rout · Answer 1 · 2013-01-22T14:22:55.717

You are getting that error because you are using End.

When you want to turn your computer off, do you click on Start Menu ~~> Shut down or do you pull the power cable out directly? :-)

Using End is very similar to pulling the cable. Don't use End. Exit the function gracefully. The End statement is basically a way of making your program crash, but without showing any error messages.

Try this. Replace these lines

If stat = 0 Then
    FileCheck = "N"
    End
Else: End
End If

with

If stat = 0 Then FileCheck = "N"
Exit Function

EDIT

BTW, are you trying to find the corresponding value of Col I in Col J? If yes, then you do not need a UDF for this. You can use Vlookup()

Use this formula =VLOOKUP(I3,I3:J38,2,0) instead of =FileCheck(I3,I3:J38)

Thanks for this. I've now used Exit function and it returns value prefectly. I'm not just trying to do a vlookup, I'm intending to loop through the data, to change value N to Y, if any values of Col I have the same filenames with values Y and N. — user2000380, Jan 23 '13 at 03:19

glh · Answer 2 · 2013-01-22T14:24:31.167

1

I think your first input type should be a string not a range:

V as string

Additional you need to use exit function when you want to end the function however I'm not sure if you want to end the function, end the if statement or the for loops?

edited Jan 22 '13 at 14:24

answered Jan 22 '13 at 14:05

glh

4,900
3
23
40

I've replaced the end statment with Exit for (also tested with exit function), the result is returned as expected. Thanks. – user2000380 Jan 23 '13 at 03:19

A value used in the formula is of wrong data type when calling a UDF

2 Answers2

Linked