Nested do while loop in for loop for keyword search in columns only executing once VBA

Question

I am trying to make a keyword search loop that can scan abstracts of research papers that are imported into excel using a different program. The abstracts of these research papers are all in column K, and when I run the code I get only the keyword(0) copied into another sheet. I've rearranged the keywords and proven that the search does in fact work for the first rendition, but the for loop itself does not execute, or the while loop ends after the first execution.

 Dim LSearchRow As Integer
       Dim LCopyToRow As Integer
        Dim keyword(3) As String
        Dim i As Integer

   On Error GoTo Err_Execute
   LSearchRow = 2
   LCopyToRow = 2

    keyword(0) = "financial crisis"
    keyword(1) = "credit default swap"
    keyword(2) = "market manipulation"
    keyword(3) = "financial crisis"
    Sheets("Research").Select

   For i = LBound(keyword) To UBound(keyword)
       While Len(Range("K" & CStr(LSearchRow)).Value) > 0
            If InStr(1, Range("K" & CStr(LSearchRow)).Value, keyword(i)) > 0 Then

             'Select row in Sheet to copy
             Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
             Selection.Copy

             'Paste row into Sheet in next row
             Sheets("Research").Select
             Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
             ActiveSheet.Paste

             'Move counter to next row
             LCopyToRow = LCopyToRow + 1

             'Go back to Sheet2 to  

             Sheets("Research").Select

          End If
          LSearchRow = LSearchRow + 1
       Wend
   Next i

It seems like you're pasting on the `Research` sheet - so I'm not sure why you have the 2nd `Sheets("Research").Select`, should that be a different sheet? That said, using `Select`, and `Selection` is discouraged. See [How to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And always qualify which `Worksheet` a `Range` is on. — BigBen, Sep 11 '18 at 20:01

score 0 · Answer 1 · answered Sep 11 '18 at 21:14

Try this:

Dim rwSearch As Range
Dim rwCopy As Range
Dim keyword 'variant
Dim i As Long, k

Set rwSearch = Sheets("Research").Rows(2)
Set rwCopy = Sheets("Results").Rows(2)   '<< not "Research" ?

keyword = Array("financial crisis", "credit default swap", _
               "market manipulation", "financial crisis")

Do While Len(rwSearch.Cells(1, "K")) > 0
    k = rwSearch.Cells(1, "K").Value
    For i = LBound(keyword) To UBound(keyword)
        If InStr(1, k, keyword(i)) > 0 Then
            rwSearch.Copy rwCopy
            Set rwCopy = rwCopy.Offset(1, 0)
            Exit For '<<< already copied this row
        End If
    Next i
    Set rwSearch = rwSearch.Offset(1, 0)
Loop

Nested do while loop in for loop for keyword search in columns only executing once VBA

1 Answers1