'"sheet2"工作表没用,"遗漏值归1"没看懂,如果都存在这个值应该为0
'"后为空白,并且最后列降序排列",为空怎么去降序。先猜一个,,,
Option Explicit
Sub test()
Dim arr, brr, i, j, mark, t
mark = Split("c,1,2,3,4,5,6,7,8,9,a,b", ",")
With Sheets("sheet1")
arr = .Range("a2:a" & .[a1].End(xlDown).Row).Resize(, 76).Value
End With
With Sheets("sheet3")
.[b2].Resize(.[a1].End(xlDown).Row - 1, 75).ClearContents
brr = .Range("a2:a" & .[a1].End(xlDown).Row).Resize(, 76).Value
For i = 1 To UBound(arr, 1)
For j = 2 To UBound(arr, 2)
If Len(arr(i, j)) Then
t = mark(arr(i, j) Mod ((UBound(mark)) + 1))
If j = 2 Then
If InStr(brr(i, 1), t) Then
brr(i, j) = 0
Else
brr(i, j) = 1
End If
Else
If InStr(brr(i, 1), t) Then
brr(i, j) = 1
' brr(i, j) = brr(i, j - 1) - 1
Else
brr(i, j) = brr(i, j - 1) + 1
End If
End If
End If
Next
Next
.[a2].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End With
End Sub |