Duplicates in column

Hi all I have a userform which I have linked a combobox upto column "A" in
my worksheet.
What I have done is place some code that copies the information in this box
when exited into two postions in the workbook.

The problem I am having is when something new is added it is ok and it just
adds to the list, but when an older item is used it places it into the list
aswell so I then start having duplicates.
Is there a code I can use on the combobox to stop this from occuring?

If not how can I change this code to search the whole column and remove the
duplicates.


This is the code I use to put the information into the worksheets, It is
the section for sheet150 which is called "dayoptions" where I am having the
problem with duplicates.

Private Sub CommandButton1_Click()

R = 46
ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
R = 46
ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
R = 46
ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
R = 46
ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)

R = 46
ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
R = 46
ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
Sheet150.Range("A1").Insert
R = 46
ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
R = 46
ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)

R = 1
Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
Sheet150.Range("A1").Insert
R = 1
Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
With Worksheets("dayoptions")
.Range("A1:A65536").Sort Key1:=.Range("A1")
End With

Unload Me
DAYOPTIONSDAYS.Show

end sub

This is the code I use to remove the duplicate as you can see it is very
complicated, there must be an easier way.

Sub SHUTDOWN()
'
' SHUTDOWN Macro
' Macro recorded 19/08/2005 by Greg
'

'
Sheets("DAYOPTIONS").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select
ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("B1").Select
Selection.Copy
Range("B2:B500").Select
ActiveSheet.Paste
Range("C1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
Range("C1").Select
Selection.Copy
Range("C2:C500").Select
ActiveSheet.Paste
Call TRY

End Sub
Sub TRY()
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 3
rw = 1
With Worksheets("DAYOPTIONS")
Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete
End If
Next
Call TRYER

End Sub
Sub TRYER()
Columns("B:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 2
rw = 1
With Worksheets("DAYOPTIONS")
Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "0" Then
cell.EntireRow.Delete
End If
Next

Columns("B:C").Select
Range("B247").Activate
Selection.ClearContents
Range("A247").Select
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 1
Range("A1").Select
End Sub

Sorry for the long question.
Thanks in advance

Greg

Hi Greg,

Why not use the Advanced Filter | Unique Records and use the filtered list
as your source?


---
Regards,
Norman



"browie" <[email protected]> wrote in message
news:[email protected]...
> Hi all I have a userform which I have linked a combobox upto column "A" in
> my worksheet.
> What I have done is place some code that copies the information in this
> box when exited into two postions in the workbook.
>
> The problem I am having is when something new is added it is ok and it
> just adds to the list, but when an older item is used it places it into
> the list aswell so I then start having duplicates.
> Is there a code I can use on the combobox to stop this from occuring?
>
> If not how can I change this code to search the whole column and remove
> the duplicates.
>
>
> This is the code I use to put the information into the worksheets, It is
> the section for sheet150 which is called "dayoptions" where I am having
> the problem with duplicates.
>
> Private Sub CommandButton1_Click()
>
> R = 46
> ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
> R = 46
> ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
> R = 46
> ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
> R = 46
> ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)
>
> R = 46
> ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
> R = 46
> ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
> Sheet150.Range("A1").Insert
> R = 46
> ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
> R = 46
> ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)
>
> R = 1
> Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
> Sheet150.Range("A1").Insert
> R = 1
> Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
> With Worksheets("dayoptions")
> .Range("A1:A65536").Sort Key1:=.Range("A1")
> End With
>
> Unload Me
> DAYOPTIONSDAYS.Show
>
> end sub
>
> This is the code I use to remove the duplicate as you can see it is very
> complicated, there must be an easier way.
>
> Sub SHUTDOWN()
> '
> ' SHUTDOWN Macro
> ' Macro recorded 19/08/2005 by Greg
> '
>
> '
> Sheets("DAYOPTIONS").Select
> Columns("A:A").Select
> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
> Range("B1").Select
> Selection.Copy
> Range("B2:B500").Select
> ActiveSheet.Paste
> Range("C1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
> Range("C1").Select
> Selection.Copy
> Range("C2:C500").Select
> ActiveSheet.Paste
> Call TRY
>
> End Sub
> Sub TRY()
> Dim rng As Range, cell As Range, col As Long
> Dim rw As Long
> col = 3
> rw = 1
> With Worksheets("DAYOPTIONS")
> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
> End With
> For Each cell In rng
> If LCase(cell.Value) = "yes" Then
> cell.EntireRow.Delete
> End If
> Next
> Call TRYER
>
> End Sub
> Sub TRYER()
> Columns("B:C").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("B1").Select
> Dim rng As Range, cell As Range, col As Long
> Dim rw As Long
> col = 2
> rw = 1
> With Worksheets("DAYOPTIONS")
> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
> End With
> For Each cell In rng
> If LCase(cell.Value) = "0" Then
> cell.EntireRow.Delete
> End If
> Next
>
> Columns("B:C").Select
> Range("B247").Activate
> Selection.ClearContents
> Range("A247").Select
> ActiveWindow.SmallScroll Down:=-24
> ActiveWindow.ScrollRow = 124
> ActiveWindow.ScrollRow = 1
> Range("A1").Select
> End Sub
>
> Sorry for the long question.
> Thanks in advance
>
> Greg
>
>
>

Thanks Norman
will give it a go just did not think of that at all.

Greg

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Greg,
>
> Why not use the Advanced Filter | Unique Records and use the filtered
> list as your source?
>
>
> ---
> Regards,
> Norman
>
>
>
> "browie" <[email protected]> wrote in message
> news:[email protected]...
>> Hi all I have a userform which I have linked a combobox upto column "A"
>> in my worksheet.
>> What I have done is place some code that copies the information in this
>> box when exited into two postions in the workbook.
>>
>> The problem I am having is when something new is added it is ok and it
>> just adds to the list, but when an older item is used it places it into
>> the list aswell so I then start having duplicates.
>> Is there a code I can use on the combobox to stop this from occuring?
>>
>> If not how can I change this code to search the whole column and remove
>> the duplicates.
>>
>>
>> This is the code I use to put the information into the worksheets, It is
>> the section for sheet150 which is called "dayoptions" where I am having
>> the problem with duplicates.
>>
>> Private Sub CommandButton1_Click()
>>
>> R = 46
>> ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
>> R = 46
>> ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
>> R = 46
>> ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
>> R = 46
>> ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)
>>
>> R = 46
>> ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
>> R = 46
>> ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
>> Sheet150.Range("A1").Insert
>> R = 46
>> ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
>> R = 46
>> ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)
>>
>> R = 1
>> Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
>> Sheet150.Range("A1").Insert
>> R = 1
>> Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
>> With Worksheets("dayoptions")
>> .Range("A1:A65536").Sort Key1:=.Range("A1")
>> End With
>>
>> Unload Me
>> DAYOPTIONSDAYS.Show
>>
>> end sub
>>
>> This is the code I use to remove the duplicate as you can see it is very
>> complicated, there must be an easier way.
>>
>> Sub SHUTDOWN()
>> '
>> ' SHUTDOWN Macro
>> ' Macro recorded 19/08/2005 by Greg
>> '
>>
>> '
>> Sheets("DAYOPTIONS").Select
>> Columns("A:A").Select
>> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Range("B1").Select
>> ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
>> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
>> Range("B1").Select
>> Selection.Copy
>> Range("B2:B500").Select
>> ActiveSheet.Paste
>> Range("C1").Select
>> Application.CutCopyMode = False
>> ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
>> Range("C1").Select
>> Selection.Copy
>> Range("C2:C500").Select
>> ActiveSheet.Paste
>> Call TRY
>>
>> End Sub
>> Sub TRY()
>> Dim rng As Range, cell As Range, col As Long
>> Dim rw As Long
>> col = 3
>> rw = 1
>> With Worksheets("DAYOPTIONS")
>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>> End With
>> For Each cell In rng
>> If LCase(cell.Value) = "yes" Then
>> cell.EntireRow.Delete
>> End If
>> Next
>> Call TRYER
>>
>> End Sub
>> Sub TRYER()
>> Columns("B:C").Select
>> Selection.Copy
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> SkipBlanks _
>> :=False, Transpose:=False
>> Range("B1").Select
>> Dim rng As Range, cell As Range, col As Long
>> Dim rw As Long
>> col = 2
>> rw = 1
>> With Worksheets("DAYOPTIONS")
>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>> End With
>> For Each cell In rng
>> If LCase(cell.Value) = "0" Then
>> cell.EntireRow.Delete
>> End If
>> Next
>>
>> Columns("B:C").Select
>> Range("B247").Activate
>> Selection.ClearContents
>> Range("A247").Select
>> ActiveWindow.SmallScroll Down:=-24
>> ActiveWindow.ScrollRow = 124
>> ActiveWindow.ScrollRow = 1
>> Range("A1").Select
>> End Sub
>>
>> Sorry for the long question.
>> Thanks in advance
>>
>> Greg
>>
>>
>>
>
>

I would suggest that before you add the item to the list you check if it is
there already, that way you list should remain duplicate free without having
to clear up afterwards. If you need to sort the lsit do that at the same
time. So the pseudo code would be....

combox value is entered then
check is value in list
if not add it to the end, sort the list, and refresh the combo box, set
index to value entered

If you wish to pursue this let me know and I'll provide some code.


--
Cheers
Nigel



"browie" <[email protected]> wrote in message
news:[email protected]...
> Hi all I have a userform which I have linked a combobox upto column "A" in
> my worksheet.
> What I have done is place some code that copies the information in this
box
> when exited into two postions in the workbook.
>
> The problem I am having is when something new is added it is ok and it
just
> adds to the list, but when an older item is used it places it into the
list
> aswell so I then start having duplicates.
> Is there a code I can use on the combobox to stop this from occuring?
>
> If not how can I change this code to search the whole column and remove
the
> duplicates.
>
>
> This is the code I use to put the information into the worksheets, It is
> the section for sheet150 which is called "dayoptions" where I am having
the
> problem with duplicates.
>
> Private Sub CommandButton1_Click()
>
> R = 46
> ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
> R = 46
> ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
> R = 46
> ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
> R = 46
> ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)
>
> R = 46
> ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
> R = 46
> ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
> Sheet150.Range("A1").Insert
> R = 46
> ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
> R = 46
> ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)
>
> R = 1
> Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
> Sheet150.Range("A1").Insert
> R = 1
> Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
> With Worksheets("dayoptions")
> .Range("A1:A65536").Sort Key1:=.Range("A1")
> End With
>
> Unload Me
> DAYOPTIONSDAYS.Show
>
> end sub
>
> This is the code I use to remove the duplicate as you can see it is very
> complicated, there must be an easier way.
>
> Sub SHUTDOWN()
> '
> ' SHUTDOWN Macro
> ' Macro recorded 19/08/2005 by Greg
> '
>
> '
> Sheets("DAYOPTIONS").Select
> Columns("A:A").Select
> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
> Range("B1").Select
> Selection.Copy
> Range("B2:B500").Select
> ActiveSheet.Paste
> Range("C1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
> Range("C1").Select
> Selection.Copy
> Range("C2:C500").Select
> ActiveSheet.Paste
> Call TRY
>
> End Sub
> Sub TRY()
> Dim rng As Range, cell As Range, col As Long
> Dim rw As Long
> col = 3
> rw = 1
> With Worksheets("DAYOPTIONS")
> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
> End With
> For Each cell In rng
> If LCase(cell.Value) = "yes" Then
> cell.EntireRow.Delete
> End If
> Next
> Call TRYER
>
> End Sub
> Sub TRYER()
> Columns("B:C").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("B1").Select
> Dim rng As Range, cell As Range, col As Long
> Dim rw As Long
> col = 2
> rw = 1
> With Worksheets("DAYOPTIONS")
> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
> End With
> For Each cell In rng
> If LCase(cell.Value) = "0" Then
> cell.EntireRow.Delete
> End If
> Next
>
> Columns("B:C").Select
> Range("B247").Activate
> Selection.ClearContents
> Range("A247").Select
> ActiveWindow.SmallScroll Down:=-24
> ActiveWindow.ScrollRow = 124
> ActiveWindow.ScrollRow = 1
> Range("A1").Select
> End Sub
>
> Sorry for the long question.
> Thanks in advance
>
> Greg
>
>
>

Thanks Nigel
I would like some help with that just not sure how to?

Greg
"Nigel" <[email protected]> wrote in message
news:[email protected]...
>I would suggest that before you add the item to the list you check if it is
> there already, that way you list should remain duplicate free without
> having
> to clear up afterwards. If you need to sort the lsit do that at the same
> time. So the pseudo code would be....
>
> combox value is entered then
> check is value in list
> if not add it to the end, sort the list, and refresh the combo box, set
> index to value entered
>
> If you wish to pursue this let me know and I'll provide some code.
>
>
> --
> Cheers
> Nigel
>
>
>
> "browie" <[email protected]> wrote in message
> news:[email protected]...
>> Hi all I have a userform which I have linked a combobox upto column "A"
>> in
>> my worksheet.
>> What I have done is place some code that copies the information in this
> box
>> when exited into two postions in the workbook.
>>
>> The problem I am having is when something new is added it is ok and it
> just
>> adds to the list, but when an older item is used it places it into the
> list
>> aswell so I then start having duplicates.
>> Is there a code I can use on the combobox to stop this from occuring?
>>
>> If not how can I change this code to search the whole column and remove
> the
>> duplicates.
>>
>>
>> This is the code I use to put the information into the worksheets, It is
>> the section for sheet150 which is called "dayoptions" where I am having
> the
>> problem with duplicates.
>>
>> Private Sub CommandButton1_Click()
>>
>> R = 46
>> ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
>> R = 46
>> ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
>> R = 46
>> ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
>> R = 46
>> ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)
>>
>> R = 46
>> ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
>> R = 46
>> ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
>> Sheet150.Range("A1").Insert
>> R = 46
>> ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
>> R = 46
>> ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)
>>
>> R = 1
>> Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
>> Sheet150.Range("A1").Insert
>> R = 1
>> Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
>> With Worksheets("dayoptions")
>> .Range("A1:A65536").Sort Key1:=.Range("A1")
>> End With
>>
>> Unload Me
>> DAYOPTIONSDAYS.Show
>>
>> end sub
>>
>> This is the code I use to remove the duplicate as you can see it is very
>> complicated, there must be an easier way.
>>
>> Sub SHUTDOWN()
>> '
>> ' SHUTDOWN Macro
>> ' Macro recorded 19/08/2005 by Greg
>> '
>>
>> '
>> Sheets("DAYOPTIONS").Select
>> Columns("A:A").Select
>> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
>> _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Range("B1").Select
>> ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
>> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
>> Range("B1").Select
>> Selection.Copy
>> Range("B2:B500").Select
>> ActiveSheet.Paste
>> Range("C1").Select
>> Application.CutCopyMode = False
>> ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
>> Range("C1").Select
>> Selection.Copy
>> Range("C2:C500").Select
>> ActiveSheet.Paste
>> Call TRY
>>
>> End Sub
>> Sub TRY()
>> Dim rng As Range, cell As Range, col As Long
>> Dim rw As Long
>> col = 3
>> rw = 1
>> With Worksheets("DAYOPTIONS")
>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>> End With
>> For Each cell In rng
>> If LCase(cell.Value) = "yes" Then
>> cell.EntireRow.Delete
>> End If
>> Next
>> Call TRYER
>>
>> End Sub
>> Sub TRYER()
>> Columns("B:C").Select
>> Selection.Copy
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> SkipBlanks _
>> :=False, Transpose:=False
>> Range("B1").Select
>> Dim rng As Range, cell As Range, col As Long
>> Dim rw As Long
>> col = 2
>> rw = 1
>> With Worksheets("DAYOPTIONS")
>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>> End With
>> For Each cell In rng
>> If LCase(cell.Value) = "0" Then
>> cell.EntireRow.Delete
>> End If
>> Next
>>
>> Columns("B:C").Select
>> Range("B247").Activate
>> Selection.ClearContents
>> Range("A247").Select
>> ActiveWindow.SmallScroll Down:=-24
>> ActiveWindow.ScrollRow = 124
>> ActiveWindow.ScrollRow = 1
>> Range("A1").Select
>> End Sub
>>
>> Sorry for the long question.
>> Thanks in advance
>>
>> Greg
>>
>>
>>
>
>

Not sure where your "value" is coming from, or what column/cell it is going
to. But in general

If Worksheetfunction.Countif(Sheets("MySheet"),Range("A:A"),"value") = 0
then
' code to add item
Else msgbox "Item already exists"
' what do you want to do
End If

--
steveB

Remove "AYN" from email to respond
"browie" <[email protected]> wrote in message
news:[email protected]...
> Thanks Nigel
> I would like some help with that just not sure how to?
>
> Greg
> "Nigel" <[email protected]> wrote in message
> news:[email protected]...
>>I would suggest that before you add the item to the list you check if it
>>is
>> there already, that way you list should remain duplicate free without
>> having
>> to clear up afterwards. If you need to sort the lsit do that at the same
>> time. So the pseudo code would be....
>>
>> combox value is entered then
>> check is value in list
>> if not add it to the end, sort the list, and refresh the combo box, set
>> index to value entered
>>
>> If you wish to pursue this let me know and I'll provide some code.
>>
>>
>> --
>> Cheers
>> Nigel
>>
>>
>>
>> "browie" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi all I have a userform which I have linked a combobox upto column "A"
>>> in
>>> my worksheet.
>>> What I have done is place some code that copies the information in this
>> box
>>> when exited into two postions in the workbook.
>>>
>>> The problem I am having is when something new is added it is ok and it
>> just
>>> adds to the list, but when an older item is used it places it into the
>> list
>>> aswell so I then start having duplicates.
>>> Is there a code I can use on the combobox to stop this from occuring?
>>>
>>> If not how can I change this code to search the whole column and remove
>> the
>>> duplicates.
>>>
>>>
>>> This is the code I use to put the information into the worksheets, It
>>> is
>>> the section for sheet150 which is called "dayoptions" where I am having
>> the
>>> problem with duplicates.
>>>
>>> Private Sub CommandButton1_Click()
>>>
>>> R = 46
>>> ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
>>> R = 46
>>> ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
>>> R = 46
>>> ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
>>> R = 46
>>> ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)
>>>
>>> R = 46
>>> ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
>>> R = 46
>>> ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
>>> Sheet150.Range("A1").Insert
>>> R = 46
>>> ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
>>> R = 46
>>> ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)
>>>
>>> R = 1
>>> Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
>>> Sheet150.Range("A1").Insert
>>> R = 1
>>> Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
>>> With Worksheets("dayoptions")
>>> .Range("A1:A65536").Sort Key1:=.Range("A1")
>>> End With
>>>
>>> Unload Me
>>> DAYOPTIONSDAYS.Show
>>>
>>> end sub
>>>
>>> This is the code I use to remove the duplicate as you can see it is very
>>> complicated, there must be an easier way.
>>>
>>> Sub SHUTDOWN()
>>> '
>>> ' SHUTDOWN Macro
>>> ' Macro recorded 19/08/2005 by Greg
>>> '
>>>
>>> '
>>> Sheets("DAYOPTIONS").Select
>>> Columns("A:A").Select
>>> Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
>>> _
>>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>>> DataOption1:=xlSortNormal
>>> Range("B1").Select
>>> ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
>>> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
>>> Range("B1").Select
>>> Selection.Copy
>>> Range("B2:B500").Select
>>> ActiveSheet.Paste
>>> Range("C1").Select
>>> Application.CutCopyMode = False
>>> ActiveCell.FormulaR1C1 = "=IF(RC[-1]>1,""YES"","""")"
>>> Range("C1").Select
>>> Selection.Copy
>>> Range("C2:C500").Select
>>> ActiveSheet.Paste
>>> Call TRY
>>>
>>> End Sub
>>> Sub TRY()
>>> Dim rng As Range, cell As Range, col As Long
>>> Dim rw As Long
>>> col = 3
>>> rw = 1
>>> With Worksheets("DAYOPTIONS")
>>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>>> End With
>>> For Each cell In rng
>>> If LCase(cell.Value) = "yes" Then
>>> cell.EntireRow.Delete
>>> End If
>>> Next
>>> Call TRYER
>>>
>>> End Sub
>>> Sub TRYER()
>>> Columns("B:C").Select
>>> Selection.Copy
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> SkipBlanks _
>>> :=False, Transpose:=False
>>> Range("B1").Select
>>> Dim rng As Range, cell As Range, col As Long
>>> Dim rw As Long
>>> col = 2
>>> rw = 1
>>> With Worksheets("DAYOPTIONS")
>>> Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
>>> End With
>>> For Each cell In rng
>>> If LCase(cell.Value) = "0" Then
>>> cell.EntireRow.Delete
>>> End If
>>> Next
>>>
>>> Columns("B:C").Select
>>> Range("B247").Activate
>>> Selection.ClearContents
>>> Range("A247").Select
>>> ActiveWindow.SmallScroll Down:=-24
>>> ActiveWindow.ScrollRow = 124
>>> ActiveWindow.ScrollRow = 1
>>> Range("A1").Select
>>> End Sub
>>>
>>> Sorry for the long question.
>>> Thanks in advance
>>>
>>> Greg
>>>
>>>
>>>
>>
>>
>
>