I forgot to add a couple of points to my first post (sorry!)
1. You might need to change the parameters for the Find method, i.e. LookAt,
LookIn and MatchCase depending on how you match your data. This might be the
cuase of the problem.

2. After you add an item to the Master list you'll need to increase the
rngMasterData range, e.g. Set rngMasterData =
rngMasterData.Resize(RowSize:=rngMasterData.Rows+1 )

Ok, my reply to your question is, did you change the range to which
rngMasterData refers? i.e. is your Master list on Sheet2 between B2 and
B200. If so, maybe an example of the data would help.
--

Simon


"vect98" wrote:


Hi simon,

thanks for the response.

I tried your macro - modifiying the worksheets where i needed to.

Sub Add_New_Entries()

Dim rngMasterData As Range
Dim rngNewData As Range
Dim cell As Range
Dim rngEntryFound As Range

Set rngNewData =
Workbooks("book4.xls").Worksheets("Sheet1").Range( "B2:B900") ' <--
change this
Set rngMasterData = ThisWorkbook.Worksheets("Sheet2").Range("B2:B200")
'
'<-- change this

' loop through all the new data looking for a matching value in master
'data
For Each cell In rngNewData
Set rngEntryFound = rngMasterData.Find(What:=cell.Value,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If (rngEntryFound Is Nothing) Then
' The value in cell.Value does not exist in master data
' So add value to Master data
MsgBox "not found"
Else

' Do nothing
End If
Next cell

CleanUp:
If Not (rngEntryFound Is Nothing) Then Set rngEntryFound = Nothing
If Not (rngNewData Is Nothing) Then Set rngNewData = Nothing
If Not (rngMasterData Is Nothing) Then Set rngMasterData = Nothing

End Sub


I want to search the long list which is about 900 rows (could get
larger if more things are added) and compare it to the Master List and
if there are new items in the Long list add them to the master list.
WHen i ran the macro it seemed to go through the list but just come up
with "nothing found" even when i added an extra item to the Long list
so something new should've come up. I've tried to figure out how tofix
this but im not sure. your help will be greatly appreacitated.

Thanks heaps.

:)


--
vect98
------------------------------------------------------------------------
vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=396372