excel - 如果语句忽略相等

Question

场景:我有一个函数，它比较 2 个不同数组中的数据并将相应的值写入工作表。

数组1:

+-------+----------------+-----------------+
| Name1 | Current Level1 | Previous Level1 |
+-------+----------------+-----------------+
| ID1   | b              | c               |
+-------+----------------+-----------------+
| ID2   | f*             | g               |
+-------+----------------+-----------------+
| ID3   |                |                 |
+-------+----------------+-----------------+
| ID4   | e              | e               |
+-------+----------------+-----------------+

数组2:

+----+--------------+
| ID | Corresponder |
+----+--------------+
| a  | 1            |
+----+--------------+
| b  | 2            |
+----+--------------+
| c  | 3            |
+----+--------------+
| d  | 4            |
+----+--------------+
| e  | 5            |
+----+--------------+
| f* | 6            |
+----+--------------+
| g  | 7            |
+----+--------------+
| h  | 8            |
+----+--------------+
| i  | 9            |
+----+--------------+

场景:我正在运行一个循环，它读取第一个数组中的字母，在第二个数组中找到一个对应的值(如 Vlookup)并写入一个与第一个数组完全相同的数组，但使用相应的数字)。

输出:

+-------+----------------+-----------------+
| Name1 | Current Level1 | Previous Level1 |
+-------+----------------+-----------------+
| ID1   | 2              | 3               |
+-------+----------------+-----------------+
| ID2   | 6              | 7               |
+-------+----------------+-----------------+
| ID3   |                |                 |
+-------+----------------+-----------------+
| ID4   | 5              | 5               |
+-------+----------------+-----------------+

问题:我正在运行这段代码，但是对于某些值，即使比较是肯定的，循环仍然会跳转内部命令。

代码:

Function match_up_values(cleanoutputArray As Variant, matchArray As Variant, targetColumn As Integer, matchColumn As Integer)

    For loopvar1 = 2 To UBound(cleanoutputArray, 1)
        For loopvar2 = 2 To UBound(matchArray, 1)
            If CStr(cleanoutputArray(loopvar1, targetColumn + 1)) = CStr(matchArray(loopvar2, ratingsColumn)) And CStr(cleanoutputArray(loopvar1, targetColumn + 1)) <> "" Then ' some times, even if this if is true, it steps to the next loopvar2
                shtOutput1.Cells(loopvar1, targetColumn + 1) = matchArray(loopvar2, matchColumn + 1)
                shtOutput1.Cells(loopvar1, 1) = cleanoutputArray(loopvar1, 1)
                shtOutput1.Cells(loopvar1, 2) = cleanoutputArray(loopvar1, 2)
                Exit For
            End If
        Next loopvar2
    Next loopvar1

End function

问题:什么可能导致此错误？

Answer 1

此代码不能解决您的问题。但是，它是一个可以帮助您解决问题的小工具:

Function stringcompare(strA As String, strB As String)
    If StrComp(strA, strB, vbTextCompare) = 0 Then stringcompare = "Text match (case insensitive)"
    If StrComp(strA, strB) = 0 Then stringcompare = "Perfect match"
    If Len(strA) <> Len(strB) Then stringcompare = "Length of strings not matching"
    If StrComp(Trim(strA), Trim(strB), vbTextCompare) = 0 Then stringcompare = "Text match (case insensitive) but with padding"
    If StrComp(Trim(strA), Trim(strB)) = 0 Then stringcompare = "Perfect match but with padding"
    If stringcompare <> "" Then Exit Function
    Dim i As Long
    For i = 1 To Len(strA)
        stringcompare = stringcompare & "('" & Mid(strA, i, 1) & IIf(Mid(strA, i, 1) = Mid(strB, i, 1), "'='", "'<>'") & Mid(strB, i, 1) & "') "
    Next
End Function

在正确的时间在正确的地方使用它可以让您看到导致两个字符串不匹配的原因。