vb在excel中搜索数据
发布网友
共3个回答
热心网友
'引用：
'添加对该对象的引用 工程菜单-->引用，找到 Microsoft Scripting Runtime
'引用microsoft Excel 14.0 object library
'
Dim elApp As Excel.Application
Dim elBooks As Excel.Workbook
Dim ekSheet As Excel.Worksheet
Dim TblMap_Card '创建一个变量
Private Sub Command1_Click()
Dim i As Integer
openEl
Set dic = CreateObject("Scripting.Dictionary")
'MsgBox ekSheet.Cells(Rows.Count, 1).End(3).Row
For i = 2 To ekSheet.Cells(Rows.Count, 1).End(3).Row
If dic.Exists(ekSheet.Cells(i, 2).Value) Then
dic(ekSheet.Cells(i, 1).Value) = dic(ekSheet.Cells(i, 1).Value) + ekSheet.Cells(i, 2).Value
Else
dic(ekSheet.Cells(i, 1).Value) = ekSheet.Cells(i, 2).Value
End If
Next i
ekSheet.Range("H:J").Clear
'ekSheet.Cells(1, 9).Resize(1, 2) = Array("商品", "售量")
ekSheet.Cells(2, 9).Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
ekSheet.Cells(2, 10).Resize(dic.Count, 1) = Application.Transpose(dic.Items)
End Sub
Private Sub openEl()
Dim myPath As String
myPath = "\week.xlsx"
Set elApp = CreateObject("Excel.Application")
Set elBooks = elApp.Workbooks.Open(App.Path & myPath)
Set ekSheet = elBooks.Worksheets("Sheet1")
'Set ekSheet = elBooks.Worksheets(1)
elApp.Visible = True
End Sub
VB6的，测试通过。界面上只有一个按钮。
热心网友
Dim Range As Range
Set Range = xlsheet.Cells.Find(what:="20", lookat:=xlWhole) 'xlWhole表示单元格匹配
If Not Range Is Nothing Then
x = Range.Row
y = Range.Column
End If
热心网友
Cells.find，然后再获得range.row和range.column
