Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    look up duplicates in column (excel/vba 2003)

    I need to be able to find out if a certain number has already been entered into the spreedsheet. What I'm looking for here is that when the user types in a SD number, if it's a duplicate then msgbox "sd number has already been entered " or something of that sort. Here is my code so far:

    ws.Cells(irow, 3).Value = Me.txtSdnumber.Value
    If Me.txtSdnumber = "" Then
    Me.txtSdnumber.SetFocus
    MsgBox "Please enter the Service Desk Number!"
    Exit Sub
    End If

    Can anybody show me how to do this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: look up duplicates in column (excel/vba 2003)

    Should the code look for duplicates anywhere in the sheet, or in a specific row or column? If so, which row or column?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look up duplicates in column (excel/vba 2003)

    COLUMN C

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: look up duplicates in column (excel/vba 2003)

    You could use code like this:
    <code>
    Dim strValue As String
    Dim rng As Range

    strValue = Me.txtSdnumber

    ' Has the user entered a value?
    If strValue = "" Then
    Me.txtSdnumber.SetFocus
    MsgBox "Please enter the Service Desk Number!"
    Exit Sub
    End If

    ' Does the value already occur in column C?
    Set rng = ws.Range("C:C").Find(What:=strValue, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    Me.txtSdnumber.SetFocus
    MsgBox "This Service Desk Number has already been used!"
    Exit Sub
    End If

    ws.Cells(irow, 3) = strValue</code>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: look up duplicates in column (excel/vba 2003)

    great it works just like I needed thank you, Sir.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •