求excel2007宏代码筛选单元格里的数组导出为出现的数字

发布网友发布时间：2022-08-15 01:47

共2个回答

热心网友时间：2023-09-11 02:04

Sub aa()
Dim Arr(1 To 40), Fs As String, Ns()
Dim Rng As Range
For Each Rng In Range("A:A")
    If Rng = "" Then Exit Sub
    For i = 1 To Len(Rng) Step 2
        Fs = Mid(Rng, i, 2)
        Arr(Fs) = 1
    Next
    For j = 1 To 40
        If Arr(j) = "" Then
            n = n + 1
            ReDim Preserve Ns(1 To n)
            Ns(n) = Format(j, "00")
        End If
    Next
    Sheet2.Range("A1").Resize(n, 1) = WorksheetFunction.Transpose(Ns)
Next
End Sub

热心网友时间：2023-09-11 02:05

用公式就可实现，

在sheet2的A1里输入公式：

=SUBSTITUTE(TEXT(SMALL(IF(ISNUMBER(MATCH(ROW($1:$40),--MID(Sheet1!A$1,ROW($1:$50)*2-1,2),0)),4^8,ROW($1:$40)),ROW(A29)),"00"),"65536","")

按CTAL+SHIFT+回车结束输入，然后向下拉动就行了。

求excel2007宏代码 筛选单元格里的数组导出为出现的数字

求excel2007宏代码筛选单元格里的数组导出为出现的数字