Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to check for text (Excel 97 SR-2)

    I have a worksheet that contains employee data that was generated from a financial package. As part of the processing I need a macro to check a range to be sure the information is valid. The range is variable in length and never has blank cells. It always starts in cell D2 and goes down at least 600 rows. A valid entry has to be Text that looks like numbers (Ideally it should also check the length -so far all have to be 3 chars long). Valid entries could be 000 through 999. I want the macro to count the number of entries that are not valid and display it in a messagebox after all cells in the range are checked. I started a macro to test for text (not length) and did not have much luck. The macro is named CheckInfo. A sample workbook is attached.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

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

    Re: Macro to check for text (Excel 97 SR-2)

    I don't know of a watertight way to determine whether a cell contains a number "as text", in particular if the data were generated by another application. The following works in your example, but I'm sure that it will fail in other spreadsheets. Declare an extra string variable:

    Dim strProblemCells As String

    And change the loop in your code to:

    For Each rCell In rng
    If IsNumeric(rCell) And rCell.NumberFormat = "@" Then
    TextOK = TextOK + 1 ' What do you do with this?
    Else
    TextProblem = TextProblem + 1
    strProblemCells = strProblemCells & vbCrLf & rCell.Address(False, False)
    End If
    Next
    MsgBox "Problems: " & TextProblem & strProblemCells

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to check for text (Excel 97 SR-2)

    Hans,

    Works GREAT! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Thank you ever so much.
    The TextOK part was just garbage -I decided I did not care if it was OK, just if it was bad (When I ran into trouble with the macro, I just did not delete that part).

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to check for text (Excel 97 SR-2)

    You could also set 'rng' without using a rangename, such as:

    On Error Resume Next
    Set rng = Range([d2], Cells(Application.Rows.Count, Columns("D").Column).End(xlUp))
    If Not rng Is Nothing Then
    ' code loop here
    End If
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to check for text (Excel 97 SR-2)

    Would Hans' code catch the error when the cell contains "_701" (i.e. an extraneous space in the cell)?

    Here's my attempt at a solution. I've taken the liberty of adding color coding to distinguish valid and invalid codes.
    <pre>Sub CheckInfo()

    Dim rng As Range
    Dim rCell As Range
    Dim TextProblem, TextOK, Good, Bad As Integer


    Sheets("EmplData").Select 'Goto Sheet named EmplData
    Range("D2").Select 'Make C2 active cell
    Range(ActiveCell, ActiveCell.End(xlDown)).Select 'Select cells with Orgs
    Selection.Name = "SubClassList" 'Name the selected range
    Set rng = Range("SubClassList")
    TextProblem = 0
    TextOK = 0

    For Each rCell In rng
    Good = 0: Bad = 1
    On Error GoTo Problem
    If Application.IsText(rCell) And Len(rCell) = 3 And CInt(rCell) < 1000 Then
    Good = 1: Bad = 0
    rCell.Interior.ColorIndex = 34
    End If
    Count:
    TextOK = TextOK + Good
    TextProblem = TextProblem + Bad
    Next
    MsgBox "Problems: " + Format(TextProblem, "#") + " OK: " + Format(TextOK, "#")
    Exit Sub
    Problem:
    On Error GoTo 0
    rCell.Interior.ColorIndex = 6
    Resume Count
    End Sub
    </pre>


  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to check for text (Excel 97 SR-2)

    Range(ActiveCell, ActiveCell.End(xlDown)).Select

    will stop at the first blank cell, hence the top-down & bottom-up approach I used above (which could probably be improved further.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    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: Macro to check for text (Excel 97 SR-2)

    I decided to let excel figure it whihc cells were text!
    I set the whole range and count them
    Then I select the cells with constants that are text (which eliminates the numbers). Then I subtract this value to give me the count of the "Numbers"

    Then I check for the length
    then loop throug the 3 to make sure they are all text-numbers"
    Counting invalid as I go.

    In addition to counting you could also change color the invalid and uncolor the valid ones

    Steve
    <pre>Option Explicit

    Sub CheckInvalid()
    Dim rng As Range
    Dim rCell As Range
    Dim lNumberInvalid As Long
    Dim x As Integer
    Set rng = Range(Range("d1"), Range("D65536").End(xlUp))
    lNumberInvalid = rng.Cells.Count

    Set rng = rng.SpecialCells(xlCellTypeConstants, xlTextValues)
    lNumberInvalid = lNumberInvalid - rng.Cells.Count

    For Each rCell In rng
    If Len(rCell.Value) <> 3 Then
    lNumberInvalid = lNumberInvalid + 1
    Else
    For x = 1 To 3
    If Asc(Mid(rCell.Value, 1, 1)) < 48 Or _
    Asc(Mid(rCell.Value, 1, 1)) > 57 Then
    lNumberInvalid = lNumberInvalid + 1
    Exit For
    End If
    Next
    End If
    Next
    If lNumberInvalid <> 0 Then _
    MsgBox "There are " & Format(lNumberInvalid, "0") & _
    " Invalid Entries in the Selection"
    End Sub</pre>


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

    Re: Macro to check for text (Excel 97 SR-2)

    Excellent idea to use SpecialCells! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    For some reason, I always forget SpecialCells (I suspect that "some reason" is age)

  9. #9
    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: Macro to check for text (Excel 97 SR-2)

    Hans,
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> for the thumbs up.
    I also have problems remembering it (I run into that age problem too- the "old-gray matter" just isn't as pliable as it once was).

    A tip to posters (I figure Hans already knows this)
    "SpecialCells" can be a great way to limit the number of items you have to loop through (less to test, more efficient), and can also "filter the list" so you can eliminate some of your "validation checks" altogether- another way to make your testing faster and your code cleaner.

    Steve

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to check for text (Excel 97 SR-2)

    I was playing with Columns("D").SpecialCells() and a couple of other ideas, but you guys are too fast! One thing I had was that
    Application.WorksheetFunction.COUNT(D)
    tells you the number of numeric cells without any messing about setting ranges. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    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: Macro to check for text (Excel 97 SR-2)

    I also used the range to limit the number of items I had to check so it served a dual purpose.

    Steve

Posting Permissions

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