Q:

Copy rows which corresponds to search data

My project has old information on Sheet1 and I import new data onto Sheet2. Column A (on both sheets) contains a 4 digit number. What I'm trying to do is find the row on Sheet1 that has the same 4 digit number as my new information on Sheet2 (to ensure I'm updating the correct information) and overwrite the old entry with the new (I also have it highlighting if there's been a date change, but that's not critical at this point; dates are in column E). Also, if there is no corresponding entry on Sheet1, I want to be able create a new entry in the next available row. The code that I've written so far does OK for one row, but has problems that I'm having trouble getting past:

The Do While loop will run forever when there is no match.
I can't figure out how to loop through all of the cells I want to search on Sheet1 as well as all of my search terms on Sheet2 (I was thinking I had to check every cell with info on Sheet1 Col A for each search term on Sheet2, but from everything I've seen online, it seems there has to be a better way, but I'm too green to figure it out).

Code:
Private Sub DoWork()
    Dim billOr As Range
    Dim billTgt As Range
    Dim tgtCell As Range
    Dim orCell As Range
    Dim compareBill As Integer
    Dim compareDate As Integer
    Dim x As Integer
    Dim i As Integer

    i = 1
    x = 2
    Set billOr = Sheets("Sheet2").Range("A" & i)
    Set billTgt = Sheets("Sheet1").Range("A" & x)
    Set orCell = Sheets("Sheet2").Range("E" & i)
    compareBill = InStr(billOr.Value, billTgt.Value)

    Do While compareBill <> 1
        compareBill = InStr(billOr.Value, billTgt.Value)
        Set billTgt = billTgt.Offset(1, 0)
    Loop

    Set tgtCell = Sheets("Sheet1").Range("E" & x)
    compareDate = InStr(orCell, tgtCell)

    If compareDate = 0 Then
        tgtCell.EntireRow.Value = orCell.EntireRow.Value
        tgtCell.EntireRow.Interior.ColorIndex = 6
    Else
        tgtCell.EntireRow.Value = orCell.EntireRow.Value
    End If
End Sub

Any help would be appreciated, even if it's just pointing me in the right direction.

A:

Ignoring the date part: 
Private Sub DoWork()

    Dim billOr As Range
    Dim billTgt As Range
    Dim shtDest as Worksheet

    Set billOr = Sheets("Sheet2").Range("A1")
    Set shtDest = Sheets("Sheet1")

    Do While billOr.value <> ""

        Set billTgt = shtDest.columns(1).find(billOr.value, _
                                              lookat:=xlwhole)
        If billTgt Is Nothing Then
            Set billTgt = shtDest.cells(rows.count,1) _
                          .End(xlUp).Offset(1,0)
            Debug.Print "copying new row to " & billTgt.Address()
        End If

        billOr.entirerow.copy billTgt

        Set billOr = billOr.Offset(1,0)
    Loop 

End Sub

