Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to Bob Phillips | Excel Discussion (Misc queries) | |||
For Bob Phillips | Excel Worksheet Functions | |||
Sync Code | Excel Programming | |||
Bob Phillips | Excel Programming | |||
ProgressBar Out of Sync | Excel Programming |