Greetings,
Currently Im struggling to create a search engine in vba that compares user IDs 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]
Currently Im struggling to create a search engine in vba that compares user IDs 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]
Code:
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
