title: search combination of words in worksheet using VBA
body: <p>I need to find the row which as combination of words (Keyword - Column 1 , keyword 2 - column 2 ,  keyword 3 - column 3) of sheet1  with sheet 2 which as more than 800 rows and 275 columns.</p>
  
  <p>I have done a coding but it gives a result as "not responding". Please help me to sort out this issue.</p>
  
  <p>below is the coding:-</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  Dim keyword As String
  Dim keyword1 As String
  Dim keyword2 As String
  Dim keyword3 As String
  Dim k As Long
  Dim k1 As Long
  
  Application.ScreenUpdating = False
  
  
  Set XML = ThisWorkbook.Worksheets("XML")
  Set rn = XML.UsedRange
  
  k = rn.Rows.Count + rn.Row - 1
  Debug.Print (k)
  For i = 1 To k
  
  k1 = rn.Columns.Count + rn.Column - 1
  Debug.Print (k1)
  For j = 1 To k1
  
  cellAYvalue = XML.Cells(i, j)
  
  For a = 2 To 261
  
  MatchAttempt = 0
  
  keyword_Flag = False
  keyword1_Flag = False
  keyword2_Flag = False
  keyword3_Flag = False
  keyword4_Flag = False
  keyword5_Flag = False
  
  keyword = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 2)))
  keyword1 = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 3)))
  keyword2 = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 4)))
  keyword3 = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 5)))
  keyword4 = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 6)))
  keyword5 = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 7)))
  
  If keyword &lt;&gt; "" Then
      keyword_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  If keyword1 &lt;&gt; "" Then
      keyword1_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  If keyword2 &lt;&gt; "" Then
      keyword2_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  If keyword3 &lt;&gt; "" Then
      keyword3_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  If keyword4 &lt;&gt; "" Then
      keyword4_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  If keyword5 &lt;&gt; "" Then
      keyword5_Flag = True: MatchAttempt = MatchAttempt + 1
  End If
  
  
          MatchedCount = 0
  
          Description = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          Description1 = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          Description2 = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          Description3 = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          Description4 = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          Description5 = Trim(UCase(cellAYvalue = XML.Cells(i, j)))
          EXITloop = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 1)))
  
          If EXITloop = "" Then
          Exit For
          End If
  
  
                MatchComplete = False
  
                If keyword_Flag = True Then
                  If keyword = Description Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
                  If keyword_Flag1 = True Then
                  If keyword1 = Description1 Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
                If keyword_Flag2 = True Then
                  If keyword2 = Description2 Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
                If keyword_Flag3 = True Then
                  If keyword3 = Description3 Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
                If keyword_Flag4 = True Then
                  If keyword4 = Description4 Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
                If keyword_Flag5 = True Then
                  If keyword5 = Description5 Then
                      MatchedCount = MatchedCount + 1
                      If MatchAttempt = MatchedCount Then MatchComplete = True
                  End If
                End If
  
  
                  inin = Trim(UCase(ThisWorkbook.Worksheets("XML").Cells(i, 112)))
                  ouou = Trim(UCase(ThisWorkbook.Worksheets("Keyword").Cells(a, 8)))
  
  
              If MatchComplete = True Then
  
                      ouou = inin
  
              End If
  
  
  a = a + 0
  
  Next
  
  j = j + 0
  
  Next
  
  i = i + 0
  
  Next
  
  Application.ScreenUpdating = True
  
  MsgBox "Completed"
  
  End Sub
  </code></pre>
  
  <hr>
  
  <p><strong>Edit: More details</strong></p>
  
  <hr>
  
  <p>I have a workbook with two worksheets</p>
  
  <p>Sheet 1 is having “N” number of data with 807 rows and 277 column</p>
  
  <p>Sheet 2 is having the standard keyword combination (201 combinations) set.</p>
  
  <p>Note: - each combination from sheet 2 can be available in any of the row or columns of sheet 1, But combination match should be in row wise alone.</p>
  
  <p>Requirements: - Need to find the keyword combination from sheet 2 in sheet 1 once the combination found in sheet 1 we need to fetch the output.</p>
  
  <p><strong>Sheet 1 (Data Sheet)</strong></p>
  
  <p><a href="https://i.stack.imgur.com/dgkQf.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dgkQf.jpg" alt="Sheet1"></a></p>
  
  <p><strong>Sheet 2 (Keyword Sheet)</strong></p>
  
  <p><a href="https://i.stack.imgur.com/b9ABx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b9ABx.jpg" alt="Sheet2"></a></p>
  
  <p><strong>Searching keywords from sheet 2 in sheet 1</strong></p>
  
  <p><a href="https://i.stack.imgur.com/qKFSc.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qKFSc.jpg" alt="Search"></a></p>
  
  <p><strong>keywords can find in many cells of sheet 1 (Yellow highlighted) but the combination will be find in only one row and we need to find that row (Green highlighted)</strong></p>
  
  <p><a href="https://i.stack.imgur.com/hsjAg.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hsjAg.jpg" alt="K"></a></p>
  
  <p>once we have found the row in sheet 1 which has the combination we need to fetch the fourth value from the last combination word and paste it in the 10th column of sheet 2.</p>
  
  <p>E.g</p>
  
  <p>in sheet 1</p>
  
  <p>we have found the combination 100th row</p>
  
  <p>in that row keyword 1 in (100,20)
  keyword 2 in (100,40)
  keyword 3 in (100,60)</p>
  
  <p>then output should be need to copy the value from cell (100,64) in sheet 1 then need to paste in 10th column of sheet 2 to respective combination row of sheet 2.</p>