having a problem finding a solution

[James]

Hello all,

I have posted this question to two other boards, some of you may have seen it. I have done a search on the boards w/ no sucess. Simply, what is the vba code to read a Column and check for duplicates in that column.

i.e. with sheet"Invoice" commandbutton click copies the info to sheet "summary". Problem, every time the commandbutton is clicked it creates an error "a duplicate entry". I want to inform the enduser w/ a MsgBox that they have created a duplicate entry.

Any help on this subject would be greatly appreciated.

Regards
James

[phantom1975]

Give this a whirl:

Private Sub CommandButton1_Click()
Dim MyColl As New Collection
Dim ThisCell As Range
Dim SearchCol As Range
Dim r As Integer
    On Error Resume Next
    r = 1
    Set SearchCol = Sheets("Sheet1").Range("C1:C100")
    For Each ThisCell In SearchCol
        If ThisCell.Value <> "" Then
            MyColl.Add ThisCell.Value, ThisCell.Value
            End If
        If Err.Number <> 0 Then
            If MsgBox("You have a duplicate entry in cell " & ThisCell.Address & ".  Do you wish to delete it?", vbYesNo + vbCritical, "Duplicate Entry") = vbYes Then
                ThisCell.ClearContents
                End If
        Else
            Sheets("Sheet2").Range("A" & r).Value = ThisCell.Value
            r = r + 1
            End If
        Err.Clear
        Next ThisCell
End Sub

[XL-Dennis]

Hi James,

I have posted this question to two other boards, some of you may have seen it.

Yes.... I dislike cross-posting very much and I don't help with this kind of post at all. :x :x

[phantom1975]

I understand how frustrating it can be when you need help and find that you just can't get it. I will honestly tell you that this board really has some of the most talented people you will ever find that freely offer their experience and knowledge. XL-Dennis certainly being one of the most admired and talented people in my mind. I certainly invite you to use every resource you can to get the help and knowledge you need, however, I think you will find that your frustrations will be well resolved when you post here.

[James]

Dennis,

I posted to a "help Forum" not an attitude / your opinion
forum if you have a problem w/ someone posting to differant forums looking for help/ a solution, and don't want to help....so be it. I don't care if you choose not to help me. It's Mr. Excel not xldennis.

Oh please Dennis forgive me , I promise you I will only post to this board and this board only.

Regards,
james

[James]

Thanks Phantom,

I tried your code, adjusted the sheet names and column range. It didn't seem to work? Thanks for taking the to to help.

Regards
James

[Tom Urtis]

James --

Hello to a fellow Californian.

Not trying to step on Phantom's assistance, but can I ask please, what are you trying to actually achieve? Do you only want to know if a value you add from the Invoice list to the Summary list is a duplicate of one that already exists in the Summary list? Or do you want code that will check first if that "about-to-be-copied" value is a duplicate, and not copy it after all into the Summary list?

You have plenty of options but if you could please give a bit more detail about what you want, maybe outlining it as "if this is the case do this; if that is the case do that", I am sure we could find a solution that would help reach your objective.

Try to specify what you actually want to have happen if a duplicate is found. Example, do you want the user to have the option of copying the value anyway? Or do you want to disallow the copying and just provide a message box telling the user that the value already exists and the macro will not copy it?

Thanks.

[James]

Hello Tom,

The commandbutton on sheet"Invoice" upon click copies and paste the info to sheet"summary". If the end
user for whatever reason clicks on the commandbutton
more than once would create a duplicate entry(s). What I'm trying to acheive is some bit of code that upon commandbutton click would check sheet "summary"
column f and if it finds a match would show the MsgBox
stating so....and what I was thinking instead of deleteing
the duplicate automatically, on click of say O.K. activate sheet"summary" and allow the end user to decide if they want to delete the entry or leave it.

Thanks for taking the time, appreciated

Regards
James

[Tom Urtis]

OK, two more follow-up questions:

(1)

You wrote:

"The commandbutton on sheet"Invoice" upon click copies and paste the info to sheet"summary". "

Is the code attached to your command button meant to copy an entire list in a range of cells, or just one cell...what is the nature of "the info" being copied?

(2)
What are the possible involved row-and-column ranges on each sheet?

[James]

Hello Tom,

I'm creating this application for my neighbor to automate his invoicing. I've been at this since 4:30 A.M. our time, and I'm beat. Would you mind if I e- you the file?

Regards
James