|
1.根据表头(AB2---AM2)名称
匹配M列数据,当(AB2---AM2)名称与M列数则在当行填入“1”,空值进填“0”
2.统计(AB-AM)重复出现的次数
将结果在AO更输出
数据比较多,大概有13W多条数据,希望能用数组来实现
- Sub dome()
- Dim arr(), brr, crr, i, j
- brr = Sheet1.Range("M3:M255")
- crr = Sheet1.Range("AB2:AM2")
- ReDim arr(1 To UBound(brr), 1 To UBound(crr, 2))
- For i = 1 To UBound(brr)
- For j = 1 To UBound(crr, 2)
- If brr(i, 1) = crr(1, j) Then
- arr(i, j) = 1
- Else
- arr(i, j) = 0
- End If
- Next
- Next i
- Sheet1.Range("ab3").Resize(UBound(brr), UBound(crr, 2)) = arr
- Sheet1.Range("ap2") = "=SUM(OFFSET(AB:AB,,ROW(A1)-1))"
- Sheet1.Range("ap2:ap13").FillDown
- End Sub
复制代码
|
|