excel - 如何使用 Find 函数查找值?
问题描述
如何使用 Find 函数查找值?
我想将特定数据从外部 Excel 文件复制到当前工作簿。
我添加了 Option Explicit 来测试错误,但它可能只是发现我没有声明变量。输出是一样的。
Sub ReadDataFromCloseFile()
'
' ReadDataFromCloseFile Macro
'
'
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = ThisWorkbook
Dim src As Workbook
' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Set src = Workbooks.Open("C:\test.xlsm", True, True)
Dim masterRow_count As Integer
masterRow_count = wb.Worksheets("Sheet1").Range("A1").End(xlDown).Row
Dim row_number As Integer
row_number = 2
Dim strSearch As String
Dim searchrange As Range
Do
Dim result As Range
strSearch = wb.Worksheets("Sheet1").Range("A" & row_number).Value
Set searchrange = src.Worksheets("Sheet1").Range("D:D")
Set result = searchrange.Find(what:=strSearch, LookIn:=xlValues, lookat:=xlValues)
If Not result Is Nothing Then
'Get the data from Asiamiles
src.Worksheets("Sheet1").Range("AB" & result.Row).Copy wb.Worksheets("Sheet1").Range("B", row_number)
src.Worksheets("Sheet1").Range("J" & result.Row).Copy wb.Worksheets("Sheet1").Range("C", row_number)
src.Worksheets("Sheet1").Range("I" & result.Row).Copy wb.Worksheets("Sheet1").Range("D", row_number)
src.Worksheets("Sheet1").Range("N" & result.Row).Copy wb.Worksheets("Sheet1").Range("E", row_number)
src.Worksheets("Sheet1").Range("AD" & result.Row).Copy wb.Worksheets("Sheet1").Range("F", row_number)
src.Worksheets("Sheet1").Range("P" & result.Row).Copy wb.Worksheets("Sheet1").Range("G", row_number)
src.Worksheets("Sheet1").Range("Q" & result.Row).Copy wb.Worksheets("Sheet1").Range("H", row_number)
End If
row_number = row_number + 1
Loop Until row_number = masterRow_count
src.Close SaveChanges:=False
Set src = Nothing
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
还有另一个问题。它无法关闭 Excel 工作簿。但这还不是最大的问题。
解决方案
LookAt:=xlValues
应该是LookAt:=xlPart
或者LookAt:=xlWhole
,Range("B", row_number)
应该是Range("B" & row_number)
Option Explicit
Sub ReadDataFromCloseFile()
Const SRC_WB = "C:\test.xlsm"
Dim wb As Workbook, wbSrc As Workbook
Dim ws As Worksheet, wsSrc As Worksheet
Dim masterRow_count As Long, row_number As Long
Dim rngSearch As Range, rngResult As Range, strSearch As String
Dim i As Long, n As Long, ar, t0 As Single
t0 = Timer
' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Application.ScreenUpdating = False
Set wbSrc = Workbooks.Open(SRC_WB, True, True)
Set wsSrc = wbSrc.Worksheets("Sheet1")
With wsSrc
i = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rngSearch = wsSrc.Range("D1:D" & i)
End With
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
ar = Split("AB,J,I,N,AD,P,Q", ",")
With ws
masterRow_count = .Range("A" & .Rows.Count).End(xlUp).Row
For row_number = 2 To masterRow_count
strSearch = .Range("A" & row_number).Value
Set rngResult = rngSearch.Find(what:=strSearch, _
LookIn:=xlValues, lookat:=xlWhole)
If Not rngResult Is Nothing Then
'Get the data from Asiamiles
For i = 0 To UBound(ar)
.Cells(row_number, "B").Offset(0, i) = wsSrc.Cells(rngResult.Row, ar(i))
Next
n = n + 1
End If
Next
End With
wbSrc.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox row_number - 1 & " rows scanned, " & _
n & " rows updated", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
推荐阅读
- android - 使用不同的卡片样式滚动时,recyclerView 会混淆
- matlab - 为什么我的数据中的高斯性存在问题,我该如何解决?
- ios - 为什么我的应用在 iPhone12 mini 上的显示尺寸不正确?
- calendar - 创建链接到团队频道的事件 - Microsoft Api Graph
- c# - C# Salesforce MetaData API 插入 PickList 值
- python - 更改为包含 MDDataTable --kivy 的屏幕后,MDList 的按钮卡住
- xml - 从节点中删除命名空间,然后使用 xslt 将该节点复制到新节点中
- java - 如何在带有验证注释的 bean 属性的测试用例中引发 ConstraintValidationException?
- android - 如果字符之间没有出现Android字符串资源变量
- python-3.x - python txt文件到多个txt文件