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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.