[已解决]怎样用VBA数组筛选

爱疯

FlkTotsH.rar (6.09 KB, 下载次数: 4)

2009-9-14 20:44 上传

点击文件名下载附件
怎样用数组筛选

如题，怎样筛选出数学及格的记录，存到sheet2。

谢谢！

PS：又没说清楚，我是想知道，怎样用VBA做这个。

[此贴子已经被作者于2009-9-14 21:01:57编辑过]

最佳答案

月排行榜 / 总排行榜

oobird

2009-9-14 23:30

Sub x()
Dim a, arr, i%, j%
a = Sheet1.UsedRange
ReDim arr(1 To UBound(a), 1 To 4)
For i = 1 To UBound(a)
If a(i, 3) >= 60 Or AscW(a(i, 3)) > 57 Then
m = m + 1
For j = 1 To 4
arr(m, j) = a(i, j)
Next
End If
Next
Sheet2.[a1].Resize(m, 4) = arr
End Sub

跳转到最佳答案楼层

雨狐

高级筛选

mygirl3866

EIx6sfxQ.rar (3.98 KB, 下载次数: 6)

2009-9-14 20:51 上传

点击文件名下载附件
怎样用数组筛选

在表二

领风飞翔

=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C$2:$C$27>59,ROW(Sheet1!$C$2:$C$27),65536),ROW(A1)))&""

数组

[em01][em01][em01]

领风飞翔

[em04]不会是VBA中的数组吧？

hxqzccct

貌似楼主是想用VBA

爱疯

谢谢各位！

我纠正过来了[em04]

[此贴子已经被作者于2009-9-14 21:04:45编辑过]

amulee

Sub MyFilter()
    Dim ArrYS, ArrJG, i&, j&, K&
    ArrYS = Sheet1.Range("A1:D" & Sheet1.Range("A65536").End(xlUp).Row)
    ReDim ArrJG(1 To 4, 0 To 0)
    For j = 1 To 4
        ArrJG(j, 0) = ArrYS(1, j)
    Next j
    K = 0
    For i = 2 To UBound(ArrYS, 1)
        If ArrYS(i, 3) >= 60 Then
            K = K + 1
            ReDim Preserve ArrJG(1 To 4, 0 To K)
            For j = 1 To 4
                ArrJG(j, K) = ArrYS(i, j)
            Next j
        End If
    Next i
    Sheet2.Range("A1").Resize(UBound(ArrJG, 2) + 1, 4) = Application.Transpose(ArrJG)
End Sub

[此贴子已经被作者于2009-9-14 21:59:42编辑过]

oobird

Sub x()
Dim a, arr, i%, j%
a = Sheet1.UsedRange
ReDim arr(1 To UBound(a), 1 To 4)
For i = 1 To UBound(a)
If a(i, 3) >= 60 Or AscW(a(i, 3)) > 57 Then
m = m + 1
For j = 1 To 4
arr(m, j) = a(i, j)
Next
End If
Next
Sheet2.[a1].Resize(m, 4) = arr
End Sub

zsd5237

学习

[em02][em02]