[已解决]根据条件做出筛选

zss7758258

根据 i 1 和 i 2 单元格的条件，做出筛选结果到E列，
请分别给出2个宏代码，
图片演示的是：筛选D2单元格到E2的结果
麻烦老师们了，谢谢

2种方式的提取.zip (164.37 KB, 下载次数: 9)

2015-4-11 00:05 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

xdragon

2015-4-11 01:04

1. Sub 不包含方式的提取()
   
2.     Call 获取数据源("不包含方式的提取", False)
   
3. End Sub
   
4. 
   
5. Sub 包含方式的提取()
   
6.     Call 获取数据源("包含方式的提取", True)
   
7. End Sub
   
8. 
   
9. Sub 获取数据源(SheetName As String, include As Boolean)
   
10.     Dim raw As Variant
    
11.     With Sheets(SheetName)
    
12.         raw = .Range("D2:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    
13.         .Range("E2").Resize(UBound(raw)).ClearContents
    
14.         .Range("E2").Resize(UBound(raw)) = GetFilterString(raw, include, .Range("i1").Value, .Range("i2").Value)
    
15.     End With
    
16. End Sub
    
17. 
    
18. Function GetFilterString(RawData As Variant, FilterString As Boolean, BeginStr As String, EndStr As String)
    
19.     Dim i As Long
    
20.     For i = 1 To UBound(RawData)
    
21.         If InStr(RawData(i, 1), BeginStr) > 0 Then
    
22.             If FilterString = False Then
    
23.                 RawData(i, 1) = Split(Split(RawData(i, 1), BeginStr)(1), EndStr)(0)
    
24.             Else
    
25.                 RawData(i, 1) = BeginStr & Split(Split(RawData(i, 1), BeginStr)(1), EndStr)(0) & EndStr
    
26.             End If
    
27.         Else
    
28.             RawData(i, 1) = ""
    
29.         End If
    
30.     Next
    
31.     GetFilterString = RawData
    
32. End Function

复制代码

跳转到最佳答案楼层

xdragon

1. Sub 不包含方式的提取()
   
2.     Call 获取数据源("不包含方式的提取", False)
   
3. End Sub
   
4. 
   
5. Sub 包含方式的提取()
   
6.     Call 获取数据源("包含方式的提取", True)
   
7. End Sub
   
8. 
   
9. Sub 获取数据源(SheetName As String, include As Boolean)
   
10.     Dim raw As Variant
    
11.     With Sheets(SheetName)
    
12.         raw = .Range("D2:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    
13.         .Range("E2").Resize(UBound(raw)).ClearContents
    
14.         .Range("E2").Resize(UBound(raw)) = GetFilterString(raw, include, .Range("i1").Value, .Range("i2").Value)
    
15.     End With
    
16. End Sub
    
17. 
    
18. Function GetFilterString(RawData As Variant, FilterString As Boolean, BeginStr As String, EndStr As String)
    
19.     Dim i As Long
    
20.     For i = 1 To UBound(RawData)
    
21.         If InStr(RawData(i, 1), BeginStr) > 0 Then
    
22.             If FilterString = False Then
    
23.                 RawData(i, 1) = Split(Split(RawData(i, 1), BeginStr)(1), EndStr)(0)
    
24.             Else
    
25.                 RawData(i, 1) = BeginStr & Split(Split(RawData(i, 1), BeginStr)(1), EndStr)(0) & EndStr
    
26.             End If
    
27.         Else
    
28.             RawData(i, 1) = ""
    
29.         End If
    
30.     Next
    
31.     GetFilterString = RawData
    
32. End Function

复制代码

zss7758258

xdragon 发表于 2015-4-11 01:04

{:1612:}老师出手不凡，一击即中