excel - VBA: How to find search value from Sheet "DMR" and then from found search value row copy cell at column A and cell at Column D into Sheet "Search"

Question

This is my first time asking for help on any VBA programming sites. I am very new to VBA programming (had some experience 10 years ago) and am trying to create a document cross reference tool for work in which the user can easily search for a document number and see where that document number is referenced in other documents. I am using Excel 2010.

Over the past 3 days scouring websites, and reading Excel VBA programming for dummies (me) a coworker loaned to me, this is the code I have currently written, which successfully comes up with the desired inquiry box, but I can not seem to get the search inquiry to work, or the copy paste commands to work.

I am trying my utmost to be respectful of this site's rules, and demonstrate my efforts at trying to write this code without simply getting someone else to do all the work, but I obviously need help:

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

If there is anything else I can provide, or some way of conveying the information I am trying to acquire more clearly, please don't hesitate to ask!

Thank-you so much for your patience!

Veronica

Answer 1

This searches the desired range (G3:EP7002) in a loop to find all instances and will drop it in Sheet(Search) starting at A5:B5. It lacks the error checking of user3578951 but I leave you to figure that out ^_^

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