检查列是否匹配值的数组

Question

我想寻求您对我的问题的帮助：

我想检查工作簿 A 的 Q 列中的行值是否为“WIN”。

如果是，则在工作簿 A 的 BL 列的相应行中返回 1。

如果否，则在工作簿 A 的 BL 列的相应行中返回 0。

我已经应用了基于 VBA 的数组来执行检查，但不幸的是，我只得到 1，而不是 0...

我的（非工作）代码如下：

Dim ws As Worksheet
Dim j, LastRowOutcomeCleaned As Long
Dim arrQ, arrBL As Variant
Dim answer, found As Range
  
Set ws = Workbooks("A.xlsx").Sheets(2)
LastRow = ws.Range("Q" & Rows.Count).End(xlUp).Row

arrQ = ws.Range("Q2:Q" & LastRow).Value 
arrBL = ws.Range("BL2:BL" & LastRow).Value 

ReDim arrBL(1 To UBound(arrQ), 1 To 1) 

For j = 1 To UBound(arrQ)
    answer = "WIN"
    
    Set found = ws.Columns("Q:Q").Find(what:=answer)
    
    If found Is Nothing Then
        arrBL(j, 1) = "0"  
    Else
        arrBL(j, 1) = "1" 
    End If
Next j

ws.Range("BL1").Resize(UBound(arrBL), 1).Value = arrBL 

非常感谢！

Answer 1

Find 每次都会在循环中搜索整个列，因此如果该值存在于列中的任何位置，整个输出数组将返回 1。如果您想要逐行测试，则测试每一行：

Dim ws As Worksheet
Dim j As Long, LastRowOutcomeCleaned As Long
Dim arrQ as Variant, arrBL As Variant
Dim answer as string

'Note: this is dangerous as the order of sheets may change
'      Consider using the codename instead.  
Set ws = Workbooks("A.xlsx").Sheets(2) 
LastRow = ws.Range("Q" & Rows.Count).End(xlUp).Row

arrQ = ws.Range("Q2:Q" & LastRow).Value 
'Next line not needed as you redim it right after.
'arrBL = ws.Range("BL2:BL" & LastRow).Value 

ReDim arrBL(1 To UBound(arrQ), 1 To 1) 

For j = 1 To UBound(arrQ)
    answer = "WIN"
    
    If arrQ(j,1)=answer Then
        arrBL(j, 1) = 1  
    Else
        arrBL(j, 1) = 0 
    End If
Next j

ws.Range("BL1").Resize(UBound(arrBL), 1).Value = arrBL