VBA code to search string from Sheet1 matching Sheet2

February 8, 2018 at 11:03:16
Specs: Windows 10
Hello Dear friend,
I am struggling to make my Macro code working, Could you please help?
In sheet1 (File1) I have 5 columns , but in column A I have value to use as a search string.
In sheet2(File2) I have 5 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, I want add a new column in Sheet1(File1) and place Msg “Matched”) in corresponded to search string row
Else place “Unmached” in corresponded row .
Is it possible?
Thank you in advance for your help!!!!
Example of the two files in workbook
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 to search string from Sheet1 matching Sheet2

Report •

#1
February 8, 2018 at 11:18:07
Before we address your issue, I would like to offer a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to format example data and VBA code so that they are easier for us to read. Then edit your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

In addition, please repost your VBA code, per the How-To instructions, so that the indents are maintained.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 8, 2018 at 11:57:15
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

 Files1
Server Name	Serv	Server IP	Server Function	  Match Sheet2 Status
FRUP4945	MDM	10.248.57.169	Prod DB node 1	  Found	    
FRUP4946	MDM	10.248.57.170	Prod DB node 2	  Found	 
FRUP4947	MDM	10.248.57.171	Prod DB node 3	  Not Found

File2
Home Tran Server IP Server1 Server2
Window MDM 10.248.57.169 FRUP4945 DFT5654 Window NDM 10.248.57.169 DB node 2 FRUP4946
Window MDM 10.248.57.169 DB node 3 GFTRD555


Report •

#3
February 8, 2018 at 12:03:42
I'm confused between the text of your question and the VBA code itself.

In the text of your post you said:

If match found, I want add a new column in Sheet1(File1) and place Msg “Matched”) in corresponded to search string row Else place “Unmached” in corresponded row .

However, your code doesn't seem to have any instructions related to the strings Matched or Unmatched. It seems to be copying data from one sheet to the other.

You said that your code is "not working" which makes sense since it's not written to do anything close to what you asked about. Why is there such a difference between what you say you want to do and what the code is actually doing?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
February 8, 2018 at 12:05:24
Your code is still not posted correctly. Use the pre tags just like you did for your data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
February 8, 2018 at 12:43:31
Sorry for confusion, the not working code is related to the search string from File1, looping thru the File2. When condition met I did write to another sheet (result), but this is not my biggest issue now, I want the search working resolve first and copy matching line in result sheet as a temp step. Later I want to add column STATUS in sheet1 , but not in this code. Thank you, I submitting my code again.


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 as temp for debugging. Change as applicable
If Not rngCopy Is Nothing Then rngCopy.Copy Sheets("File1").Rows(1)
End With
End Sub



Report •

#6
February 8, 2018 at 13:22:02
Because of the way you posted your File2 data, you make it difficult to determine the exact issue. It could be one of the 2 things, which I will explain later, but first I have to point out the importance of posting your example data according to the instructions found in the How-To link.

In my very first response I said "Don't forget to use Column letters and Row numbers". You did not do that.

When I look at your File2 data in Response #2, I can't tell if it is all in Column A or if it is supposed to spread out across multiple columns. Why is that important? It's important because the problem with your code depends entirely on that answer.

Look at this instruction:

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

The first thing that I noticed is that you are searching Column A only. .Columns(1)

So, let's consider your data layout:

If all of your File2 data is in Column A, then the argument LookAt:=xlWhole needs to changed to LookAt:=xlPart.

If your File2 data is spread out across multiple columns, then you need to change .Columns(1) to .Cells so that the entire sheet will be searched.

I hope that you see the importance of formatting/explaining your data in detail. Without knowing where your data is located or how it is laid out, it's impossible for us to give you a definitive answer to why your code isn't working.

We don't point out those instructions because we want the posts to look pretty. We do it so that we understanding exactly what we are working with in order to make it possible to offer solutions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
February 8, 2018 at 15:03:48
Thank you, again, This is making sense.
I did change .Columns(1) to .Cells to search thru the entire sheet2 , but started getting error" Unable to get the FindNext property of the range class" on the last statement Set rngCopy = .Rows((aCell.Row + 1) & ":" & (aCell.Row + 2))
   
    With ws
        Set aCell = .Cells.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 

 Files1
Server Name	Serv	Server IP	Server Function	  Match Sheet2 Status
FRUP4945	MDM	10.248.57.169	Prod DB node 1	  Found	    
FRUP4946	MDM	10.248.57.170	Prod DB node 2	  Found	 
FRUP4947	MDM	10.248.57.171	Prod DB node 3	  Not Found

 Files2
Column1	        Column2	Column3	         Column4              Column5
XXXXXXX	XXX	       FRUP4945	         XXXXX        	      XXXXX    
XXXXXXX	XXX	       XXXXXXX	        FRUP4946             XXXXX	 
XXXXXXX	XXX	       XXXXXXX	         XXXXXXX             XXXXXX


Report •

#8
February 8, 2018 at 15:38:11
I would have thought that I made it pretty clear that it is very important for you to include Column letters and Row numbers when you post example data. I've said it twice in my responses and they are shown in the How-to instructions.

Since you don't seem to want to include that information, I don't think I want to offer any more advice.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
February 8, 2018 at 16:44:58
Thank you, You was a good help. You catched the most important error.
At least is working, but is not matching data from the second sheet.
This is my first time I am using this forum, so sorry if I overlooked something and did not respond correctly. Let me know if you are reconsider your reply and provide me your help as a SME, Thank you, again.

Report •

#10
February 8, 2018 at 18:34:52
The main thing that you need to think about when posting in a Help forum such as this one is that we cannot see your workbook from where we are sitting nor do we know anything about how you use the workbook. If you make it difficult to understand what you are trying to do, such as posting data that we can't understand or posting code that doesn't match the question you are asking, then we have to do more work just trying to figure out what you are asking than it would take to solve your problem.

I guess I'm just not sure how you missed my 3 requests that you include Columns letters and Row numbers when you post your data. How hard would it have been to have done this, so that we were all on the same page and working with the same data layout...

Files1

         A          B            C                     D                 E
1   Server Name	   Serv      Server IP	      Server Function    Match Sheet2 Status
2   FRUP4945	   MDM	   10.248.57.169       Prod DB node 1          Found	    
3   FRUP4946	   MDM	   10.248.57.170       Prod DB node 2	       Found	 
4   FRUP4947	   MDM	   10.248.57.171       Prod DB node 3        Not Found

Files2

       A            B             C             D                E
1   XXXXXXX	   XXX	       FRUP4945	      XXXXX            XXXXX    
2   XXXXXXX	   XXX	       XXXXXXX	      FRUP4946         XXXXX	 
3   XXXXXXX	   XXX	       XXXXXXX	      XXXXXXX          XXXXXX


Now, if you will post your complete code and tell me exactly what problem you are currently having, I'll take a look. Make sure that you post the code using the pre tags.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#11
February 9, 2018 at 09:10:54
Hello, I am running my code but getting irrelevant row data in result table, cannot catch where is the incorrect code is. I really appreciate if you can help Thank you


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim destinationWorkbook As Workbook, sourceWorkbook As Workbook
Dim FileToProcess
Set destinationWorkbook = ActiveWorkbook
'Get The File
FileToProcess = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select a file")
If FileToProcess = False Then Exit Sub

Set sourceWorkbook = Workbooks.Open(FileToProcess)
sourceWorkbook.Sheets(1).Cells.Copy destinationWorkbook.Sheets(2).Cells
sourceWorkbook.Close SaveChanges:=False

Set sourceWorkbook = Nothing
Set destinationWorkbook = Nothing
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim destinationWorkbook As Workbook, sourceWorkbook As Workbook
Dim FileToProcess
Set destinationWorkbook = ActiveWorkbook
'Get The File
FileToProcess = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select a file")
If FileToProcess = False Then Exit Sub

Set sourceWorkbook = Workbooks.Open(FileToProcess)
sourceWorkbook.Sheets(1).Cells.Copy destinationWorkbook.Sheets(3).Cells
sourceWorkbook.Close SaveChanges:=False

Set sourceWorkbook = Nothing
Set destinationWorkbook = Nothing
Application.ScreenUpdating = True
End Sub

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 = .Cells.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 = .Cells.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 test to results sheet for debugging.
         ' The original task is to update status in add column  with true or false in sheet1
        If Not rngCopy Is Nothing Then rngCopy.Copy Sheets("Results").Rows(1)
    End With
End Sub


Report •

#12
February 9, 2018 at 12:20:21
At this point, I don't think we can answer your question.

You started this thread by asking a fairly simple question related to finding matches. Now you have posted code that includes Command buttons so that users can open files. We obviously don't have those files to open, so we can't test your code against your data set. Even if we could, I don't know if we would know what is "irrelevant row data" and what isn't.

The best I can offer at this point is the following link. This link will take you to a tutorial that includes various VBA debugging techniques. You obviously have a high level of VBA expertise, so maybe you know all of the tricks and tips included, maybe you don't. In any case, if I had the files required to test your code and I knew what the output was supposed to look like, the first thing I would do is use the debugging techniques from the tutorial to try and figure out what is going on. Single Stepping and Watches are extremely powerful debugging tools.

https://www.computing.net/howtos/sh...

Good Luck!

message edited by DerbyDad03


Report •

Ask Question