'Array To Check Column For Matched Value
I would like to seek your help for my issue:
I would like to check if the row value in Column Q of Workbook A is "WIN".
If yes, then return 1 in the corresponding row in Column BL of Workbook A.
If no, then return 0 in the corresponding row in Column BL of Workbook A.
I have applied a VBA-based array to carry out the check but unfortunately, I am only getting 1, not 0...
My (non-working) code is below:
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
Many thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
