VBA Loop and adding data to a worksheet

[silvertyphoon1]

I had a quick question regarding loops and how they might search through a range and add data to another worksheet.

Here is steps to how this should work in my head but I haven't had to much luck - Thanks in advance!

1. The user presses a button on a userform

2. The code would then search through column A and if it finds the word "Cat" it would then check column D for the word "Done". If it finds "Done" it will continue on looping through until it finds one with "Cat" in column A but no "Done" in column D.

Once this is found to be true it will take the values from column E and column F in the same row and add it to the next blank row on another worksheet called Wrk7. It will also add the word "Done" to column D in the row it just completed.

3. In short the code should be searching for rows that have not had data extracted. When it finds those it extracts or copies the data over and adds the word done to show it has been.

Any help would be greatly appreciated - Thanks again

 

[mikerickson]

Something like this should work.

Sub test()
    Dim destinationSheet As Worksheet
    Dim oneCell As Range
    Set destinationSheet = ThisWorkbook.Worksheets("Wrk7")
    
    
    With ThisWorkbook.Sheets("Sheet1").Range("A:A")
        For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With oneCell
                If LCase(CStr(.Value)) = "cat" Then
                    With .Offset(0, 3)
                        If LCase(.Value) <> "done" Then
                            destinationSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = .EntireRow.Range("E1:F1").Value
                            .Value = "Done"
                        End If
                    End With
                End If
            End With
        Next oneCell
    End With
End Sub

 

[silvertyphoon1]

Thank you very much for your assistance the code worked great! I was curious if the code could be modified just a bit?

I was wondering if we could have it read through column B for the word "cat" and column D for the word "done". If it only finds "cat" then it will copy over data like this:

Column A data on sheet(1) to Column A on the sheet(wrk7)
Column E data on sheet(1) to Column D on the sheet(wrk7)
Column G data on sheet(1) to Column E on the sheet(wrk7)

Also adding the word "done" to sheet(1) just like before - Thanks again!