函数在运行时返回空白

Question

我有一个单元格H1，其中某些值动态为"10,1,7,8"，另一个单元格A1将函数调用为=FindGrade(H1,"Books")。。。当例如B的值是类似字符串的"2,11"时，它将检查单元格H1值中是否也存在2和11，但它不起作用。。。结果单元格为空。。。我正试图将函数的结果输入单元格，但单元格总是空白

这个想法是用A、B、C、D等中的每个值检查chkcell中的字符串，并生成名为Result的新值，并用它填充A1

我的代码是

Function FindGrade(chkcell As String, Eventtype As String) As String
Dim A As String
Dim B As String
Dim C As String
Dim D1 As String
Dim D2 As String
Dim D3 As String
If chkevent = "Books" Then
A = "7"
B = "2,11"
C = "5"
D1 = "4"
D2 = "8,10,12"
D3 = "6"
End If


Dim Result As String
Dim x
Dim y
Dim chkfound
'Updateby Extendoffice
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each x In Split(chkcell, ",")
For Each y In Split(A, ",")
If x.exists(y) Then
Result.Add "A"
chkfound = 1
Next y
Next x

If chkfound = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(B, ",")
If x.exists(y) Then
Result.Add "B"
chkfound = 1
Next y
Next x
End If

If chkfound = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(C, ",")
If x.exists(y) Then
Result.Add "C"
chkfound = 1
Next y
Next x
End If

If chkfound = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(D1, ",")
If x.exists(y) Then
Result.Add "D1"
chkfoundD = 1
Next y
Next x
End If

If chkfound = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(D2, ",")
If x.exists(y) Then
Result.Add "D2"
chkfoundD = 1
Next y
Next x
End If

If chkfound = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(D3, ",")
If x.exists(y) Then
Result.Add "D3"
chkfoundD = 1
Next y
Next x
End If

If chkfoundD = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(D3, ",")
If x.exists(y) Then
Result.Add "3"
chkfoundD = 1
Next y
Next x
End If

If chkfoundD = 0 Then
For Each x In Split(chkcell, ",")
For Each y In Split(D2, ",")
If x.exists(y) Then
Result.Add "2"
chkfoundD = 1
Next y
Next x
If chkfoundD = 0 Then
Result.Add "1"
End If

End If
End With

FindGrade = Result
End Function

Answer 1

这可能有助于您继续前进，但很难理解您打算如何派生FindGrade结果字符串。

对于示例代码中的D值集，它不包含任何条件逻辑假设，但在问题中未指定。

Function FindGrade(chkcell As String, Eventtype As String) As String
Dim Result                                  ' Initialize variable
Result = ""
If Eventtype = "Book" Then
For Each element In Split(chkcell, ",")  ' Evaluate each piece of chkcell
Select Case element
Case 7                             ' Condition A
Result = Result & "A"
Case 2, 11                         ' Condition B
Result = Result & "B"
Case 5                             ' Condition C
Result = Result & "C"
Case 4                             ' Condition D1
Result = Result & "1"
Case 8, 10, 12                     ' Condition D2
Result = Result & "2"
Case 6                             ' Condition D3
Result = Result & "3"
End Select
Next element
End If

FindGrade = Result
End Function