|
1 sql方法
2 Find方法,只能有一个条件查询。
3、请问还有哪些方法.
在此只讨论exxce命令和函数方法,不讨论Dictiongcry字典和SQL方法.
因为用EXCEL自身命令的好处是用union重新组合为一个Range,最简单和实用的应用是加颜色标定。还有其它的可扩展应用。
------------------------
多条件查询方法,常用SQL方法
Private Sub UserForm_Initialize()
Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim RST2 As New ADODB.Recordset
Dim strsql$
t = Sheet1.Range("a65536").End(xlUp).Row
CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
strsql = "select 柜号,姓名,编号 FROM [数据$a1:G" & t & "] "
RST.CursorLocation = 3
RST.Open strsql, CNN, 3, 3
Do While Not RST.EOF
ComboBox1.AddItem RST.Fields(0).Value
ComboBox2.AddItem RST.Fields(1).Value
ComboBox3.AddItem RST.Fields(2).Value
RST.MoveNext
Loop
RST.Close
RST2.CursorLocation = 3
RST2.Open "select 类别 FROM [数据$a1:G" & t & "] group by 类别 ", CNN, 3, 3
Do While Not RST2.EOF
ComboBox4.AddItem RST2.Fields(0).Value
RST2.MoveNext
Loop
RST2.Close
Set RST = Nothing
Set CNN = Nothing
End Sub
用Find,FindNext方法,只能是一个条件查询,其好处是查询到数据,可以用rrr.Interior.ColorIndex = 34用颜色标定。
Dim rng As Range, i As Long, r As Long
Sub test()
With ActiveSheet.Range("A:A")
Set rr = .Find("aa")
If Not rr Is Nothing Then
firstAddress = rr.Address
Do
If rng Is Nothing Then
Set rng = Range("A" & rr.Row & ":" & "B" & rr.Row)
Else: Set rng = Union(rng, Range("A" & rr.Row & ":" & "B" & rr.Row))
End If
Set rr = .FindNext(rr)
Loop While Not rr Is Nothing And rr.Address <> firstAddress
rng.Copy [e11]
For Each rrr In rng
rrr.Interior.ColorIndex = 34
Next
End If
End With
End Sub
[ 本帖最后由 ningyong58 于 2009-10-23 09:00 编辑 ] |
|