'result数组中就是满足条件的行号,cnt为总数
'先测试一下运行时间,着色用时不会很多的,,,
Option Explicit
Sub test()
Dim arr, i As Long, j As Long, k As Long, cnt As Long, t, dt
Dim col As Long, row As Long, n As Long
dt = Timer
With Sheets("sheet1")
col = .[a1].End(xlToRight).Column
row = .[a1].End(xlDown).row
ReDim pos(1 To col) As Long, mark(1 To row, 1 To 1) As String, result(1 To row) As Long
For i = 1 To col
t = .Cells(2, i).Value
If Len(t) > 0 And Not IsNumeric(t) Then
If Not IsDate(t) Then n = n + 1: pos(n) = i
End If
Next
For j = 1 To n
arr = .Cells(1, pos(j)).Resize(row).Value
For i = 2 To row
mark(i, 1) = mark(i, 1) & "," & arr(i, 1)
Next
Next
arr = Sheets("sheet2").[a1].CurrentRegion.Resize(, 1).Value
For i = 2 To row
For j = 1 To UBound(arr, 1)
If InStr(mark(i, 1), arr(j, 1)) Then cnt = cnt + 1: result(cnt) = i: Exit For
Next
Next
End With
Debug.Print Timer - dt, cnt
End Sub |