excel - 检查字符串是否在 Variant 中，然后分别将 Yes/No 结果替换为 1/0

Question

给出下面的代码，我如何检查“outArr”值是是还是否，并分别用 1 和 0 替换它们？

Sub IndexMatchFirm1()
                Dim destinationWs As Worksheet
                Set destinationWs = ThisWorkbook.Worksheets("Master")
                Dim destinationLastRow As Long
                destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
                Dim lkpArr As Variant
                lkpArr = destinationWs.Range("A5:A" & destinationLastRow).Value
               With Worksheets("MyData")
                Dim retval As Variant
                    retval = Intersect(.Range("E:E"), .UsedRange)
                    Dim mtch As Variant
                    mtch = Intersect(.Range("B:D"), .UsedRange)
                End With
                Dim outArr As Variant
                ReDim outArr(1 To UBound(lkpArr, 1), 1 To 1)
                Dim i As Long
                For i = 1 To UBound(lkpArr, 1)
                    Dim j As Long
                    For j = 1 To UBound(retval, 1)
                        If mtch(j, 3) = "FirmA" Then
                            Dim v
                            If mtch(j, 1) = lkpArr(i, 1) Then
                                v = retval(j, 1)
                                outArr(i, 1) = IIf(v = "Yes", 1, IIf(v = "No", 0, v))
                             End If
                             'If mtch(j, 1) = lkpArr(i, 1) Then
                                'outArr(i, 1) = retval(j, 1)
                                'Exit For
                             'End If  
                        End If
                    Next j
                Next i
             destinationWs.Range("L5").Resize(UBound(outArr, 1), 1).Value = outArr
End Sub

我尝试了很多方法，但似乎都没有奏效。我也不想使用“对于 myrange 中的每个单元格”方法，因为它会大大减慢工作簿的速度。有什么建议吗？

谢谢

标签： excelvba

Answer 1

最简单的修复：

Dim v
'...
'...
If mtch(j, 1) = lkpArr(i, 1) Then
    v = retval(j, 1)
    outArr(i, 1) = IIf(v = "Yes", 1, IIf(v="No", 0, v)
    Exit For 'Edit - I forgot to add this....
End If
'...
'...