本帖最后由 laoau138 于 2017-7-19 12:48 编辑
不用format函数不用instr函数,只用VBA数组方法改写这个遗漏计算
Sub test()
Dim SArr1(), SArr2, Tarr(), i%, j%, n%
SArr2 = [k10:aq10]
n = [c11].End(xlDown).Row
ReDim SArr1(11 To n)
ReDim Tarr(11 To n, 1 To 33)
For i = 11 To n
For j = 3 To 8
SArr1(i) = SArr1(i) & "," & Format(Cells(i, j), "00")
Next
Next
For i = 11 To n
For j = 1 To 33
If i = 11 Then
Tarr(i, j) = IIf(InStr(1, SArr1(i), "," & Format(SArr2(1, j), "00")) > 0, 0, 1)
Else
Tarr(i, j) = IIf(InStr(1, SArr1(i), "," & Format(SArr2(1, j), "00")) > 0, 0, 1 + Tarr(i - 1, j))
End If
Next
Next
[k11].Resize(n - 10, 33) = Tarr
End Sub
- Sub aaa()
- Dim arr, brr, i&, j&
- arr = Range("c11:h" & [c65536].End(3).Row)
- ReDim brr(1 To UBound(arr), 1 To 33)
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- brr(i, arr(i, j)) = 0
- Next j
- Next i
- For i = 1 To UBound(brr)
- For j = 1 To 33
- If brr(i, j) = "" Then If i = 1 Then brr(i, j) = 1 Else brr(i, j) = brr(i - 1, j) + 1
- Next j
- Next i
- [k11].Resize(UBound(brr), 33) = brr
- End Sub
复制代码
|