Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking data accuracy (2003)

    Hi all,
    I have a column of data that I need to check the accuracy of the data it contains. The data in the column consists of unique identifiers formatted as Text that generally looks something like

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

    Re: Checking data accuracy (2003)

    You can run the following macro. I'd make a backup copy of the workbook first.

    Sub CorrectPrefix()
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim strValue As String
    Dim strLetter As String
    Dim strCorrect As String
    Dim intDigits As Integer
    Application.ScreenUpdating = False
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = 1 To lngMaxRow
    strValue = Trim(Range("A" & lngRow).Value)
    If Len(strValue) > 5 Then
    strLetter = Mid(strValue, Len(strValue) - 5, 1)
    intDigits = Val(Right(strValue, 2))
    Select Case intDigits
    Case 0 To 19
    strCorrect = "F"
    Case 20 To 59
    strCorrect = "M"
    Case 60 To 99
    strCorrect = "P"
    End Select
    If StrComp(strLetter, strCorrect, vbTextCompare) Then
    Mid(strValue, Len(strValue) - 5, 1) = strCorrect
    Range("A" & lngRow) = strValue
    Range("A" & lngRow).Characters(Len(strValue) - 5, 1).Font.Color = vbRed
    End If
    End If
    Next lngRow
    Application.ScreenUpdating = True
    End Sub

    Note: the code does not distinguish between lower case and upper case letters. If you would want it to do that, change vbTextCompare to vbBinaryCompare.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking data accuracy (2003)

    Hans - Perfect as usual. Much appreciated.
    Thanks so much for your help.

    Bill

Posting Permissions

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