VBA Code read sheet1 search in sheet2 for match

February 8, 2018 at 06:24:21
Specs: Windows 10
Hello Dear friend,
I am struggling to make my Macro code working, Could you please help?
In sheet1 (File1) I have A to J columns , in column A I have value to use as a search string.
In sheet2(File2) I have 15 columns, and I want to search thru entire sheet2( File2) for the string value from the sheet1 (File1) until the last row in file1.
If match found, add new column in Sheet1(File1) with Msg “Matched”)
Else “Unmached” in corresponded row .

Is it possible?
Thank you in advance for your help!!!!

File1:
FRSW2726 test test test Matched
FRSD2734 test test test Mached
File2
G5ts bfdte FRSD2734 rtte Ftttt2
fgtd F545 Dfr55 FRSW2726 dree


This is my code not working Sub HTH()

Dim rCell As Range
Dim rFind As Range
Dim iColumn As Integer
Dim SearchString As String

For Each rCell In Sheets("File1").Range("A2", Sheets("File1").Cells(Rows.Count, "A").End(xlUp))
SearchString = rCell.Value

Search_n_Copy (SearchString)

Next rCell
End Sub

Private Sub Search_n_Copy(strSearch As String)
Dim ws As Worksheet
Dim rngCopy As Range, aCell As Range, bcell As Range

Set ws = Worksheets("File2")

With ws
Set aCell = .Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
Set bcell = aCell

If rngCopy Is Nothing Then
Set rngCopy = .Rows((aCell.Row + 1) & ":" & (aCell.Row + 2))
Else
Set rngCopy = Union(rngCopy, .Rows((aCell.Row + 1) & ":" & (aCell.Row + 2)))
End If

Do
Set aCell = .Columns(1).FindNext(After:=aCell)

If Not aCell Is Nothing Then
If aCell.Address = bcell.Address Then Exit Do

If rngCopy Is Nothing Then
Set rngCopy = .Rows((aCell.Row + 1) & ":" & (aCell.Row + 2))
Else
Set rngCopy = Union(rngCopy, .Rows((aCell.Row + 1) & ":" & (aCell.Row + 2)))
End If
Else
Exit Do
End If
Loop
End If

'~~> I am pasting to Output sheet. Change as applicable
If Not rngCopy Is Nothing Then rngCopy.Copy Sheets("File1").Rows(1)
End With
End Sub



See More: VBA Code read sheet1 search in sheet2 for match

Report •

#1
February 8, 2018 at 08:16:00
If i where you i would also ask this question under SOFTWARE --> OFFICE SOFTWARE, there are some smart guys hanging around there, that can better help you

i5-6600K[delid]@4.814GHz/4.613GHz cache@1.38v | 2x4GB Crucial-DDR4-2133@14-14-14-28 1T 2808MHz@1.37v
ASUS Z170K
Samsung 250GB SSD 850 EVO
MSI Armor RX 570 4GB@1380c/2087m BiosMod
VS450


Report •
Related Solutions


Ask Question