Consulting

Results 1 to 12 of 12

Thread: search for any character

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location

    search for any character

    Hi,

    I basically want to search for any chracters other than numbers and semi-colons from Row 2 and column D to column AG and to put up an error if detected. Ive used this as an e.g but it only picks up numbers greater than 7. I want this but also any character other than a semicolon.
    Thanks

    [VBA]
    For Each c In Range("d2", Range("d65536").End(xlUp))
    If Not IsEmpty(c) And c.Value > 7 Then
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    End If
    Next c
    [/VBA]

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Hi bartoni,

    I have modified the sub you posted to catch the semicolon, a value greater than 7, and check for alpha. Let me know if this is incorrect.

    [VBA]
    Sub LookAtIt()
    Dim c As Range
    For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty(c) And c.Value > 7 And VarType(c.Value) <> vbString Then '<- check for numeric values only
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    ElseIf Not IsEmpty(c) And VarType(c.Value) = vbString And InStr(1, c.Value, ";") = 0 Then '<-- check for alpha without semicolons
    If MsgBox("Order of Entry; You have an alpha entry in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    End If
    MsgBox c.Value
    Next c
    End Sub
    [/VBA]
    Last edited by Tommy; 07-16-2004 at 08:14 AM. Reason: used colon instead of semicolon

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    It just seemed to list the value within column D in a msgbox for each cell down in column D. Not sure why though - any other ideas??

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Sorry that is MsgBo c.Value delete that and the MsgBox will dissapear. When you say you are looking for a semicoln, and alpha could you explain?

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    I load my sheets into a application and the values in each column are predetermined. If these are exceeded, the aplication throws the value out. Thus ive tried to find a way to determine errors before they are loaded into the application. The only values allowed in column D for ex. are 1-7 and a semicolon. Anything else i.e a character will cause an error when loading, so im trying to find any other characters present in column D. I actually have 15 columns of data but am going to modify code accordingly when I get the code in 1 column to work..

  6. #6
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    Also when i ran your code with the amended version, if there is a character in the D column, the macro stops and tries to debug.

  7. #7
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Sample data

    agfdasgfd; <- no msgbox
    agfdasgfd <- msgbox
    12 <- msgbox
    7 <- no msgbox
    ; <- no msgbox

    What version of excel are you using?

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    97, Ok in column D i want values of 1-7 and ;

    So

    agfdasgfd; <- msgbox giving errors as alpha even though there is a semicolon
    agfdasgfd <- msgbox giving errors as alpha
    12 <- msgbox as over 7
    7 <- no msgbox
    ; <- no msgbox
    7; <- no msgbox

    7, <- msgbox as comma

    Thanks

  9. #9
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Ok I am using Excel 2000 so I have to figure it out, sorry. The below sub picks up the 7, and should work.

    [VBA]
    Sub LookAtIt()
    Dim c As Range
    For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty(c) Then
    If c.Value > 7 And InStr(1, c.Value, ";") = 0 Then '<- check for numeric values only
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    ElseIf Len(CStr(c.Value)) > 1 Then
    If c.Value <= 7 And InStr(1, c.Value, ";") = 0 Then
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    End If
    End If
    End If
    Next c
    End Sub
    [/VBA]

  10. #10
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    yEP THAT WORKS BUT IF YOU PUT A CHARACTER IN THE (sorry Caps) range such as an"x" it tries to debug the code.

  11. #11
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    This should catch the x now.

    [VBA]
    Sub LookAtIt()
    Dim c As Range
    For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty(c) Then
    If Val(c.Value) > 7 And InStr(1, c.Value, ";") = 0 Then '<- check for numeric values only
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    ElseIf Len(CStr(c.Value)) > 1 Then
    If Val(c.Value) <= 7 And InStr(1, c.Value, ";") = 0 Then
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    End If
    ElseIf Len(CStr(c.Value)) = 1 Then
    If Val(c.Value) = 0 And InStr(1, c.Value, ";") = 0 Then
    If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
    c.Select
    Exit Sub
    End If
    End If
    End If
    End If
    Next c
    End Sub
    [/VBA]

  12. #12
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi, Bartoni Im confused as to what your requirements are. First you say you want to search for any values that are not a number or a semi-colon, then you say you want a number greater than 7 (as if 0-6 arent numbers)? Could you clarify exactly what the criteria is. And are you solely looking at the value of the cell or the characters within the value.

    Just some thoughts...
    1. You cannot check for a value of a cell that is in error. You should use IsError before evaluating the value of a cell.
    2. IsNumeric may be handy as this tells you whether you are dealing with a number (cannot contain ; etc). ie If IsNumeric(Cell) and >=7 then OK?
    3. Maybe the Find method may be an option but dunno yet.

Posting Permissions

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