在调用UDF时,公式中使用的值是错误的数据类型 [英] A value used in the formula is of wrong data type when calling a UDF
问题描述
例如:
Col I Col J
File1 Y
File1 N
File2 Y
File3 N
我尝试调试它,并将输入的值传递到函数变量中。
我在VBA中的知识是有限的,希望你们可以帮助我解决这个问题。
在工作表中调用的函数:
= FileCheck(I3,I3:J38)
代码
公共功能FileCheck As Range,Q As Range)As String
Dim vtest As Variant
Dim i,j,stat As Integer
stat = 0
vtest = Q
对于i = 1 To UBound(vtest)
如果V = vtest(i,1)和vtest(i,2)=N然后
对于j = 1到UBound(vtest)
如果V = vtest(j,1)和vtest(j,2)=Y然后
FileCheck =Y
stat = 1
结束如果
下一个
如果stat = 0然后
FileCheck =N
结束
其他:结束
结束如果
ElseIf V = vt est(i,1)And vtest(i,2)=Ythen
FileCheck =Y
End If
Next
End Function
您正在收到该错误,因为您使用结束
。
当您想关闭电脑时,请点击开始菜单~~>关闭
还是直接拉电源线?
使用结束
非常类似于拉电缆。不要使用 End
。正常退出该功能。 End
语句基本上是使程序崩溃的一种方式,但不显示任何错误消息。
尝试此。替换这些行
如果stat = 0然后
FileCheck =N
结束
否则:结束
结束如果
与
如果stat = 0则FileCheck =N
退出函数
编辑
BTW,您是否试图找到相应的
在 Col J
?如果是,那么您不需要UDF。您可以使用 Vlookup()
使用此公式 = VLOOKUP(I3,I3: J38,2,0)
而不是 = FileCheck(I3,I3:J38)
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
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)
这篇关于在调用UDF时,公式中使用的值是错误的数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!