Hi,
Apologies, I had trouble with indenting the code, I
tabbed and the message was transmitted before I was ready.
To reiterate:
I have 4 columns, Col A and B are new customers names and
locations, Col E and F are archived details. I need to
add new customer details to the end of the archived
lists prior to a final sort. Example
Col A Col B Col E Col F
Cust1 Loc1 Cust1 Loc1
Cust2 Loc1 Cust2 Loc1
Cust2 Loc2 Cust3 Loc1
Cust2 Loc3 Cust3 Loc2
Cust3 Loc1 Cust4 Loc1
I wish to add Cust2, Loc2 and Cust2, Loc3 to the archive.

The following code works fine if the customer's name did
not exist in the archive before but fails otherwise.
Also, I sort both lists prior to searching, do I have to
start the search at the beginning again if it fails to
find anything, please comment in code?

I would be grateful for any help and sorry again for the
messed up first message.

T.I.A.
Geoff

Code:
Sub aaaa()
Dim rng As Range, rng1 As Range, rng3 As Range
Dim cust As Range, i As Integer, firstFind As String

Set rng = Range("A1")
Set cust = Range("E1")

With Sheets(1)
Set rng1 = .Range(.Range("A1"), .Range("A1:A" & _
..Range("A65536").End(xlUp).Row))
Set rng3 = .Range(.Range("E1"), .Range("E1:E" & _
..Range("E65536").End(xlUp).Row))

i = 0
For Each rng In rng1
Set cust = rng3.Find(what:=rng, _
After:=cust, _
SearchDirection:=xlNext)
firstFind = ""
If Not cust Is Nothing Then
Do Until rng.Offset(0, 1) = cust.Offset(0, 1) _
Or cust.Address = firstFind
If firstFind = "" Then firstFind = _
cust.Address
Set cust = rng3.FindNext(After:=cust)
i = i + 1
Loop
Else
.Range("E" & rng3.Rows.Count + i) = rng
.Range("F" & rng3.Rows.Count + i) = rng.Offset _
(0, 1)
i = i + 1
Set cust = Range("E1") 'Do I have to start at _
the beginning if the list is sorted??
End If
Next rng
End With
End Sub

Geoff,

It's OK - see my reply to your message above.

HTH,
Bernie


"Geoff" <mail@gknowles wrote in message
...
Hi,
Apologies, I had trouble with indenting the code, I
tabbed and the message was transmitted before I was ready.
To reiterate:
I have 4 columns, Col A and B are new customers names and
locations, Col E and F are archived details. I need to
add new customer details to the end of the archived
lists prior to a final sort. Example
Col A Col B Col E Col F
Cust1 Loc1 Cust1 Loc1
Cust2 Loc1 Cust2 Loc1
Cust2 Loc2 Cust3 Loc1
Cust2 Loc3 Cust3 Loc2
Cust3 Loc1 Cust4 Loc1
I wish to add Cust2, Loc2 and Cust2, Loc3 to the archive.

The following code works fine if the customer's name did
not exist in the archive before but fails otherwise.
Also, I sort both lists prior to searching, do I have to
start the search at the beginning again if it fails to
find anything, please comment in code?

I would be grateful for any help and sorry again for the
messed up first message.

T.I.A.
Geoff

Code:
Sub aaaa()
Dim rng As Range, rng1 As Range, rng3 As Range
Dim cust As Range, i As Integer, firstFind As String

Set rng = Range("A1")
Set cust = Range("E1")

With Sheets(1)
Set rng1 = .Range(.Range("A1"), .Range("A1:A" & _
.Range("A65536").End(xlUp).Row))
Set rng3 = .Range(.Range("E1"), .Range("E1:E" & _
.Range("E65536").End(xlUp).Row))

i = 0
For Each rng In rng1
Set cust = rng3.Find(what:=rng, _
After:=cust, _
SearchDirection:=xlNext)
firstFind = ""
If Not cust Is Nothing Then
Do Until rng.Offset(0, 1) = cust.Offset(0, 1) _
Or cust.Address = firstFind
If firstFind = "" Then firstFind = _
cust.Address
Set cust = rng3.FindNext(After:=cust)
i = i + 1
Loop
Else
.Range("E" & rng3.Rows.Count + i) = rng
.Range("F" & rng3.Rows.Count + i) = rng.Offset _
(0, 1)
i = i + 1
Set cust = Range("E1") 'Do I have to start at _
the beginning if the list is sorted??
End If
Next rng
End With
End Sub