excel - VBA:如何从工作表 "DMR"中找到搜索值，然后从找到的搜索值行中将 A 列的单元格和 D 列的单元格复制到工作表 "Search"

Question

这是我第一次在任何 VBA 编程网站上寻求帮助。我对 VBA 编程非常陌生(10 年前有一些经验)，并且正在尝试创建一个文档交叉引用工具，用户可以在其中轻松搜索文档编号并查看该文档编号在其他文档中的引用位置。我正在使用 Excel 2010。

在过去的 3 天里搜索网站，并阅读 Excel VBA 编程傻瓜(我)一个同事借给我的，这是我目前编写的代码，它成功地提出了所需的查询框，但我似乎无法得到搜索查询工作，或复制粘贴命令工作。

我正在尽我最大的努力尊重这个网站的规则，并展示我在尝试编写这段代码而不是简单地让其他人完成所有工作的努力，但我显然需要帮助:

Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim ws As Worksheet
Dim lngLstRow As Long
Dim lngLstCol As Long
Dim strSearch As String
Dim r As Long
Dim x As Variant

strSearch = InputBox("Please enter 5 digit document number to search for (e.g. 00002):", "Search Value")

Sheets("DMR").Select
'Loop through sheet DMR and search for "search value". The search value may be in several rows, but will only appear once in a row.
For r = 1 To endRow
x = Range("G3:EP7002").Value 'yes-there are 7002 rows of data all starting at column G and potentially ending at column EP. There are many blank cells.
If Cells(r, x).Value = "Search Value" Then

'Copy the cells at column A and D of found search value row in Sheet "DMR"
Range(Cells(r, "A"), Cells(r, "D")).Select
Selection.Copy

'Switch to sheet "SEARCH" & paste two cells from sheet "DMR" into sheet "SEARCH" cells A5:B5
Sheets("SEARCH").Select
Range(r, "A5:B5").Select
ActiveSheet.Paste

'Next time you find a match in sheet "DMR", it will be pasted in the next row on sheet "SEARCH"
pasteRowIndex = pasteRowIndex + 1

'Switch back to sheet DMR & continue to search for your criteria
Sheets("DMR").Select
    End If
Next r
End Sub

如果还有什么我可以提供的，或者以某种方式更清楚地传达我想要获得的信息，请不要犹豫!

非常感谢您的耐心等待!

维罗妮卡

Answer 1

这会在循环中搜索所需范围 (G3:EP7002) 以查找所有实例，并将其从 A5:B5 开始放入 Sheet(Search) 中。它缺少 user3578951 的错误检查，但我让你弄清楚 ^_^

Private Sub CommandButton1_Click()

Dim dmr As Worksheet
Dim strSearch As String
Dim f As Variant
Dim fAddress As String
Dim fRow As Long
Dim cellA As Variant
Dim cellB As Variant

Set dmr = Worksheets("DMR")
pasteRowIndex = 5
strSearch = InputBox("Please enter 5 digit document number to search for (e.g. 00002):", "Search Value")

With dmr.Range("G3:EP7002")
    Set f = .Find(strSearch, LookIn:=xlValues)
    If Not f Is Nothing Then
        fAddress = f.Address
        Do
            fRow = f.Row
            cellA = dmr.Cells(fRow, 1).Value
            cellD = dmr.Cells(fRow, 4).Value
            Sheets("SEARCH").Cells(pasteRowIndex, 1) = cellA
            Sheets("SEARCH").Cells(pasteRowIndex, 2) = cellD
            pasteRowIndex = pasteRowIndex + 1
            Set f = .FindNext(f)
        Loop While Not f Is Nothing And f.Address <> fAddress
    End If
End With

End Sub