[已解决]VBA筛选问题

q5128977 · 发表于 2012-12-12 16:43

或者我可以全改成前面加BR，这样能处理吗？BR-8101 BR-8200 BR-6500E等这样格式

hwc2ycy · 发表于 2012-12-12 16:46

回去再折腾。
现在有事。

hwc2ycy · 发表于 2012-12-12 20:35

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And (Target.Column = 2) Then
Select Case Target.Column
Case 2
Range("a4:m65536").ClearContents
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
If Range("b1") = "全部" Then
Sql = "select * from [数据源$B3:M65536] "
Else
Sql = "select * from [数据源$B3:M65536] where 产品型号 like '%" & Range("B1") & "'"
End If
Sheets("BOM").Range("B4").CopyFromRecordset conn.Execute(Sql)
conn.Close: Set conn = Nothing
End Select
End If
End Sub

复制代码

hwc2ycy · 发表于 2012-12-12 20:46

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And (Target.Column = 2) Then
Select Case Target.Column
Case 2
Range("a4:m65536").ClearContents
Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
If Range("b1") = "全部" Then
Sql = "select * from [数据源$B3:M65536] "
Else
Sql = "select * from [数据源$B3:M65536] where 产品型号 like '%" & Range("B1") & "'"
End If
Sheets("BOM").Range("B4").CopyFromRecordset conn.Execute(Sql)
conn.Close: Set conn = Nothing
End Select
If Len(Range("b4")) > 0 Then
Dim i&
i = Range("b" & Rows.Count).End(xlUp).Row
Range("a4").FormulaR1C1 = "=IF(RC[1]<>"""",COUNT(R3C1:R[-1]C)+1,"""")"
Range("a4").AutoFill Range("a4:a" & i)
End If
End If
End Sub

复制代码

hwc2ycy · 发表于 2012-12-12 20:47

测试.rar (18.14 KB, 下载次数: 18)

q5128977 · 发表于 2012-12-13 08:49

非常感谢hwc2ycy老师的耐心指导

		自动登录	找回密码
密码			注册