Sync - Bob Phillips
 

Hey Bob you posted this code as a response to a question I
had yesterday. Thank you, it works however if the new
value added to A:A matches part of any other value in A:A
then it will not add the new value to B:B. For instance
lets say there are 100 values in A:A. One of the values
in A:A is "Randy Moore". Lets say I add a new value to
the list called "Ran". When I run the sync code it does
not add the new value "Ran" to B:B because "Ran" matches
part of one of the values already in A:A. How would this
be fixed?


Dim i As Long
Dim cLastB As Long
Dim oCell As Range

cLastB = Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Set oCell = Columns(2).Find(Cells(i, "A").Value)
If oCell Is Nothing Then
cLastB = cLastB + 1
Cells(cLastB, "B").Value = Cells(i, "A").Value
End If
Set oCell = Nothing
Next i

On Error GoTo 0


Thanks
Todd Huttenstine

Sync - Bob Phillips
 

Todd,

Change this line

Set oCell = Columns(2).Find(Cells(i, "A").Value)

to

Set oCell = Columns(2).Find(Cells(i, "A").Value, lookat:=xlWhole)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
Hey Bob you posted this code as a response to a question I
had yesterday. Thank you, it works however if the new
value added to A:A matches part of any other value in A:A
then it will not add the new value to B:B. For instance
lets say there are 100 values in A:A. One of the values
in A:A is "Randy Moore". Lets say I add a new value to
the list called "Ran". When I run the sync code it does
not add the new value "Ran" to B:B because "Ran" matches
part of one of the values already in A:A. How would this
be fixed?


Dim i As Long
Dim cLastB As Long
Dim oCell As Range

cLastB = Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Set oCell = Columns(2).Find(Cells(i, "A").Value)
If oCell Is Nothing Then
cLastB = cLastB + 1
Cells(cLastB, "B").Value = Cells(i, "A").Value
End If
Set oCell = Nothing
Next i

On Error GoTo 0


Thanks
Todd Huttenstine

Sync - Bob Phillips
 

Oh thanks.



-----Original Message-----
Todd,

Change this line

Set oCell = Columns(2).Find(Cells(i, "A").Value)

to

Set oCell = Columns(2).Find(Cells(i, "A").Value,
lookat:=xlWhole)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
Hey Bob you posted this code as a response to a
question I
had yesterday. Thank you, it works however if the new
value added to A:A matches part of any other value in
A:A
then it will not add the new value to B:B. For instance
lets say there are 100 values in A:A. One of the values
in A:A is "Randy Moore". Lets say I add a new value to
the list called "Ran". When I run the sync code it does
not add the new value "Ran" to B:B because "Ran" matches
part of one of the values already in A:A. How would
this
be fixed?


Dim i As Long
Dim cLastB As Long
Dim oCell As Range

cLastB = Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Set oCell = Columns(2).Find(Cells(i, "A").Value)
If oCell Is Nothing Then
cLastB = cLastB + 1
Cells(cLastB, "B").Value = Cells
(i, "A").Value
End If
Set oCell = Nothing
Next i

On Error GoTo 0


Thanks
Todd Huttenstine


.