VBA Search Engine Only Carries Over One Cell Instead Of The Cell's Entire Row

[PeerGuy]

Greetings,

Currently I’m struggling to create a search engine in vba that compares user ID’s on two different sheets (sheet1 & sheet2) and then returns the results of the search onto sheet3. [FONT=arial, sans-serif]So basically vba searches through Sheet2 to see if it has any matching values to Sheet1 in this case the VBA is searching for matching user IDs. Column A of Sheet1 specifically starting at A4 iand s being compared to Sheet2's Column L that starts at L4. Once a match is found between the columns of both sheets the entire row of the matched cell in sheet2 is supposed to be carried over onto sheet3 within the range of k5 to v5 and continue to k6 to v6, k7 to v7 etc. However, the current code only copies the one single matched cell and not the matched cell's entire row into the desired range of K5 to V5 in Sheet3. Is there a way to fix this? My code is listed below. Any suggestions would be greatly appreciated. Thank you, first time here but I've always heard great things. Appreciate any help that's given.[/FONT]

Option Explicit
 
Sub FindWhat()
 
Dim sFindWhat As String
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim Search As Range
Dim Addr As String
Dim NextRow As Long
Dim cl As Range
 
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet3")
 
'// This will be the row you start pasting data on Sheet3
NextRow = 5
 
For Each cl In Intersect(sh1.UsedRange, sh1.Columns("A")).Cells
    '// the value we're looking for
    sFindWhat = cl.Value
    '// Find this value in Sheet2:
    With sh2.UsedRange
        Set Search = .Find(sFindWhat, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Search Is Nothing Then
            '// Get out of here if the value is not found
            '// Do NOT Exit the sub, we'll just proceed to next cell in column A
            'Exit Sub
        Else
            '// Make sure next row in Sh3.Column("K") is empty
            While sh3.Range("K" & NextRow).Value <> ""
                NextRow = NextRow + 1
            Wend
            '// Paste the row in column K of sheet 3:
            Search.Resize(1, 12).Copy Destination:=sh3.Range("K" & NextRow)
        End If
   End With
Next
End Sub

 

[Marcelo Branco]

It seems that the data in Sheet2 are located in columns A:L.
Am i right?
If so, change this
Search.Resize(1, 12).Copy Destination:=sh3.Range("K" & NextRow)
to
Range(Search.EntireRow.Cells(1, "A"), Search).Copy Destination:=sh3.Range("K" & NextRow)

REMARK: in Find (... ) you should define the parameter LookAt as either LookAt:=xlWhole or LookAt:=xlPart, depending on what you need.
xlWhole if the cell in Sheet2 must be equal to sFindWhat; xlPart if the cell in Sheet2 contains SFindWhat.

Hope this helps

M.

 

[Marcelo Branco]

Another issue:
To restrict the search to column L (column 12) of Sheet2 change this
With sh2.UsedRange

to
With sh2.Columns(12)

M.

 

[PeerGuy]

Greetings, Marcelo first off thank you for all the support I really do appreciate it. The code originally was working fine. But then the vba debugger started picking up an error in the line "If Search Is Nothing Then". It interrupts the code execution. When I hover over it, it says Nothing=Nothing. I appreciate any input on this error. Thank you.

 

[Marcelo Branco]

Have you changed anything in the original code? If so, show us the new code.
And, please, confirm the location of the data in Sheet2

M.

 

[PeerGuy]

Here is the new code, I made the changes that you suggested. Then I started getting the error code interrupted from line of code "If Search Is Nothing Then."

Option Explicit


Sub FindWhat()


Dim sFindWhat As String
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim Search As Range
Dim Addr As String
Dim NextRow As Long
Dim cl As Range


Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet3")


'// This will be the row you start pasting data on Sheet3
NextRow = 5


For Each cl In Intersect(sh1.UsedRange, sh1.Columns("A")).Cells
    '// the value we're looking for
    sFindWhat = cl.Value
    '// Find this value in Sheet2:
    With sh2.Columns12
        Set Search = .Find(sFindWhat, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlWhole)
        If Search Is Nothing Then
        Search.Activate
            '// Get out of here if the value is not found
            '// Do NOT Exit the sub, we'll just proceed to next cell in column A
            'Exit Sub
        Else
            '// Make sure next row in Sh3.Column("K") is empty
            While sh3.Range("K" & NextRow).Value <> ""
                NextRow = NextRow + 1
            Wend
            '// Paste the row in column K of sheet 3:
            Range(Search.EntireRow.Cells(1, "A"), Search).Copy Destination:=sh3.Range("K" & NextRow)
        End If
    End With
Next
End Sub

 

[PeerGuy]

With sh2.Columns(12) is the way I have it also, I know that I was missing the parenthesis before but it's there. Along with the other changes you told me to make. Including LookAt:=xlWhole

 

[PeerGuy]

Ignore search.activate by the way. That was me trying to fix the error.

 

[Marcelo Branco]

Location of data in sheet2?

M.

 

[PeerGuy]

If you're referring to what's being searched the location in sheet2 is in column L starting at L4. What's being carried over is the entire row if the search returns an ID match. The range of the entire row is column A to column L. I'm new to this please let me know if you need more details than this. So the location of the data is being searched is in column L, the location of what's being carried over if it's a match is column A through L. I hope that answered your question if not let me know.