Question

vb在excel中搜索数据

我是用如下方法写入数据的，我想问如何搜索一个数值，并记录它的单元格坐标？
比如，我搜索20这个数，并把它的单元格坐标 赋值给(x,y)，请教教我
Dim xls As New Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlbook = xls.Workbooks.Open("d:\shuju.xls")
Set xlsheet = xlbook.Sheets("sheet1")
xlsheet.Cells(1, 1) = "aa"

Answer 1

'引用：
'添加对该对象的引用 工程菜单-->引用，找到 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的，测试通过。界面上只有一个按钮。

Answer 2

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

Answer 3

Cells.find，然后再获得range.row和range.column