ActiveCell problem- value always empty!

Hello all,

I'm currently working on a project in which I have two excel files, and I need to see (by searching for combination of last and first name) if the people contained in wkBook1 are listed in wkBook2.

I've completed *almost* all of it thus far (code below) but I'm having issues figuring out how to use ActiveCell appropriately. So far I can search through workbooks to see if a person's last name is contained, but if there are two people with the same last name the macro will break if the first one isn't matched.

I tried solving this by offsetting the "ActiveCell", but it won't enter loop.

Data to search for:
Smith, John

Data Available:
Smith, Mark <--- search will find mark, doesn't match, then stop.
Smith, John

I'm *definitely* not using ActiveCell correctly, as ActiveCell.Value is always "empty" and won't enter loop. Any ideas?

Thanks!
~Jed

Sub Compare_wkBook1_wkBook2()

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set wkBook1= objExcel.Workbooks.Open("C:\Users\Wkbook1.xls")
Set wkBook2= objExcel.Workbooks.Open("C:\Users\Wkbook2.xls")
Set workSheet_2= wkBook2.Worksheets(1)
Set workSheet_1= wkBook1.Worksheets(1)

workSheet_1.Cells(1, 8).Value = "Assert: In Database"
Set lastNameRange = workSheet_2.Range("F2")
Set firstNameRange = workSheet_2.Range("G2")

i = 2

Do Until workSheet_ActualCS_SE.Cells(i, 1).Value = ""
    lastName = workSheet_1.Cells(i, 6).Value
    firstName = workSheet_1.Cells(i, 7).Value
    
    Set objSearch = lastNameRange.Find(lastName)
    
    If objSearch Is Nothing Then
        strName = lastName & " 1 Not found"
        workSheet_1.Cells(i, 8).Value = strName
    Else
        lastNameRange.Find(lastName).Select
        lastNameRange.Find(lastName).Activate
        Do While (ActiveCell.Value = lastName)
        If ActiveCell.Offset(0, 1).Value = firstName Then
            strName = "Found: " & lastName & "," & firstName
            workSheet_1.Cells(i, 8).Value = strName
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Activate
            strName = lastName & " 2 Not found"
            workSheet_1.Cells(i, 8).Value = strName
        End If
        Loop
    End If

    i = i + 1
Loop
workSheet_1.Cells(1, 8).Value = "Complete: In Database"

End Sub

Yes but I still need to use the offset function of the active cell. I could keep finding the "next" one, but how would I then compare the item offset (0, 1) of the "findnext" result?

Originally Posted by SILVERjl

Yes but I still need to use the offset function of the active cell. I could keep finding the "next" one, but how would I then compare the item offset (0, 1) of the "findnext" result?

source: Excel VB help file

FindNext Method

Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn’t affect the selection or the active cell.

expression.FindNext(After)
expression Required. An expression that returns a Range object.

After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If this argument isn’t specified, the search starts after the cell in the upper-left corner of the range.

Remarks
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

Example
This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With