Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking uniqueness (2000)

    I'm wanting to write a function that checks whether a textbox entry on a userform has already been used in a particular column (of unique values). This is what I'm using currently:
    ____________________________________________
    Private Function NotUnique(code As String) As Boolean
    Dim i As Long
    With Worksheets("Master").UsedRange
    For i = 1 To .Rows.Count
    If code = .Cells(i, 2) Then
    NotUnique = True
    Exit Function
    End If
    Next i
    End With

    NotUnique = False

    End Function

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Checking uniqueness (2000)

    Here is a function I use that strips all characters but text a numbers and makes it upper case for comparisons.

    You can add this function to a module and use a line like:

    <pre>If UStrip(code) = UStrip(.Cells(i, 2)) Then</pre>


    to do the comparison.
    Item 1
    Item-1
    item*1
    ITEM 1
    item_1
    and a host of others will ALL be considered equal after ustrip is done since Ustrip of any of them will be "ITEM1"

    Steve

    <pre>Function UStrip(Word)
    'This function takes a string outputs an uppercase equivalent with
    ' all characters stripped but numbers and letters
    'Uppercase is used since case becomes immaterial

    Word = UCase(Word) 'Convert to Uppercase
    UStrip = "" 'Set originall as null
    For x = 1 To Len(Word) 'Act on each letter
    Character = Mid(Word, x, 1)
    If Asc(Character) >= 65 And _
    Asc(Character) <= 90 Then '65 is ASCII "A", 90 is ASCII "Z"
    'Character is a LETTER
    UStrip = UStrip & Character
    ElseIf Asc(Character) >= 48 And _
    Asc(Character) <= 57 Then '65 is ASCII "0", 90 is ASCII "9"
    'Character is a NUMBER
    UStrip = UStrip & Character
    End If
    Next x

    End Function

    </pre>


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

    Re: Checking uniqueness (2000)

    You can use the Replace function to replace all occurrences of " " by "".
    You can use UCase to convert both sides to upper case before comparing them.

    code = UCase(Replace(code, " ", ""))
    ...
    If code = UCase(.Cells(i, 2)) Then
    ...

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking uniqueness (2000)

    Thanks to both Steve and Hans.
    All that lot ought to do the trick. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

Posting Permissions

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