Results 1 to 3 of 3
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    cell content challenge (Excel 2000)

    Here's the deal:
    Its 1:00am in Newcastle England.
    Tomorrow is my last day before a 2-week well-deserved Caribbean break.
    I have a mission-critical task to complete before I go which includes the following:
    I have to test a column of cells for 'valid' entries.
    If any of the cells are 'non-blank' they must conform to the following conditions:
    1. (blank cells are OK)
    2. The cell must contain ONLY 8-digit numbers with or without a leading minus sign and be be comma separated.
    3. No alpha characters allowed.
    My estimate is there should be no more than say, 50 comma separated entries in the cell.
    I can use VBA to perform the test.
    I have adequate 'spare' columns to the right of the column to be tested.
    My gut feeling is to
    a).parse the entries into columns on the right
    [img]/forums/images/smilies/cool.gif[/img] test whether there is more than 1 parsed entry from the cell (e.g presence of a ",") (mind you the User might forget to put a comma between entries and might use extra spaces etc between them)
    c). If there is only one entry, check if its length is 8 (it fails otherwise)
    4. if there are more than 1 parsed entry, use a selection, xlEndRight thingy to loop through all cells in the parsed row range to check each one is either length 8 or, if the left first character is a minus "-", length is 9
    5. The proper requiremnt is that the 8-characters should also be numeric, although leading zeros are accepatable.

    My head hurts.
    I have to do this within an hour of turning up for work in the morning.
    I've had several glasses of wine and I'm knackered.
    Can anyone help pleeeeeeeease.

    In return I promise to help anyone with their questions on this excellent forum if I'm able.

    Thanks everyone.

    zeddy
    zeddy
    (I'm seeing double again)

  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: cell content challenge (Excel 2000)

    Try this function. Add it to a module then in a blank column add:
    =isvalid(A1)
    It will be true if the conditions are met false if not.
    It was tested with some examples, but I don't know the extent of what you might find valid. so you might want to dbl check it.

    Steve

    <pre>Option Explicit
    Function IsValid(sWord As String) As Boolean
    Dim sChar As String
    Dim sEntry As String
    Dim iLen As Integer
    Dim x As Integer
    sWord = Trim(sWord)

    iLen = 0
    IsValid = False
    If Len(sWord) < 8 Then Exit Function

    For x = 1 To Len(sWord)
    sChar = Mid(sWord, x, 1)
    If sChar = "-" And iLen <> 0 Then
    Exit Function
    ElseIf sChar = " " Or sChar = "," Then
    If iLen > 0 And iLen < 8 Then Exit Function
    iLen = 0
    ElseIf Asc(sChar) > 57 Or _
    Asc(sChar) < 48 And sChar <> "-" Then
    Exit Function
    ElseIf sChar <> "-" Then
    iLen = iLen + 1
    If iLen > 9 Then Exit Function
    End If
    Next x
    If iLen = 8 Then IsValid = True
    End Function</pre>


  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: cell content challenge (Excel 2000)

    Steve,

    This is truly brilliant. Can't wait to get to the office to test it on some live data.
    You are a star.

    zeddy

Posting Permissions

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