时间戳

首页 > 解决方案 > 函数在运行时返回空白

excel - 函数在运行时返回空白

问题描述

我有具有某些值的单元格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

标签： excelvba

解决方案

这可能会帮助您上路，但很难破译您打算如何导出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

推荐阅读