Check each delimited value to make sure it meets criteria

[philwojo]

Hello, I have a workbook that has several sheets that have data on them in the following format:

107.75|106.68|96.98

The data can be a single value, or pipe delimited and contain up to 99 instances of values. Is there an easy way to check that each value has the trailing 2 digits after the decimal place? The problem is really only when the number would end in .00, like 107.00, sometimes the trailing zero's are left off. I would like to make sure that each value in the cell contains the trailing zero's.

If there is any value in the cell that does not contain the 2 numbers after the decimal point I would like to highlight the cell red and turn the font bold and yellow.

Thanks for any assistance.

Phil

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

Sorry, no again, typo and good catch.

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

Sorry, no again, typo and good catch.

In your originally posted code line, you had this logical test...

S(X) Like "*[!0-9.]*"

I think if you change it to this that it might work for you...

S(X) Like "*[!0-9.()]*"

However, adding the parentheses adds some additional entries that can cause trouble which my above suggestion doesn't handle. My above suggestion assumes if there are parentheses, then they are used correctly. If that is not the case and erroneous entries like "()" or "((" or "(23(" or "))" or ")23)" or ")1.23(" or various other entries are possibilities, then several additional logical tests must be added to the Or'ed chain of tests to rule them out.

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

I think I can live with this as is, and ignoring those possibilities.

This works as I'm looking for. I know it won't catch all possibilities, but it definitely is a huge help, as always thanks Rick!

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

Ok, hoping to get some help on this one, I am getting an error when this code runs, but it only happens when there is only one line of data on my "Raw Data" sheet. It throws a run-time error '13': Type Mismatch

Any thoughts, the code is below, and I have bolded and made the part it highlights in red.

Sub HighlightIfNotTwoDecimalPoints()  Dim r As Long, c As Long, X As Long, StartRow As Long, StartCol As String, S() As String
  Dim ws As Worksheet, WS1 As Worksheet, WS2 As Worksheet, Data As Variant
  
Application.EnableEvents = False
Application.ScreenUpdating = False


'Checks specific cells associated with cash values to make sure that they have two zeros
'After a decimal place for values like $5.00, it also checks to make sure that there is a leading
'Zero if a cash value is less than $1, like $0.74, and if it finds those are missing
'It will highlight the cell red and turn the font color to yellow to flag the problem.


  ' Process cells on "PIF Checker Output - Horz" sheet
  StartRow = 7
  StartCol = "AD"
  Set ws = Sheets("PIF Checker Output - Horz")
  Data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol).End(xlUp))
[COLOR=#ff0000][U][B]  For r = 1 To UBound(Data)[/B][/U][/COLOR]
    S = Split(Trim(Data(r, 1)), "|")
    For X = 0 To UBound(S)
      If InStr(S(X), ".") = 0 Or S(X) Like "*." Or S(X) Like "*.#" Or S(X) Like "*.*.*" Or S(X) Like "*[!0-9.()]*" Or ("X" & S(X) Like "*[!0-9].#*") Then
        With ws.Cells(StartRow + r - 1, StartCol)
          .Interior.Color = vbRed
          With .Characters(InStr("|" & .Value & "|", "|" & S(X) & "|"), Len(S(X))).Font
            .Bold = True
            .Color = vbYellow
          End With
        End With
      End If
    Next
  Next
  ' Process cells on "Output_Vert_5_Entries" sheet
  StartRow = 31
  StartCol = "D"
  Set ws = Sheets("Output_Vert_5_Entries")
  Data = ws.Cells(StartRow, StartCol).Resize(, 5)
  For c = 1 To UBound(Data, 2)
    S = Split(Trim(Data(1, c)), "|")
    For X = 0 To UBound(S)
      If InStr(S(X), ".") = 0 Or S(X) Like "*." Or S(X) Like "*.#" Or S(X) Like "*.*.*" Or S(X) Like "*[!0-9.()]*" Or ("X" & S(X) Like "*[!0-9].#*") Then
        With ws.Cells(StartRow, Columns(StartCol).Column + c - 1)
          .Interior.Color = vbRed
          With .Characters(InStr("|" & .Value & "|", "|" & S(X) & "|"), Len(S(X))).Font
            .Bold = True
            .Color = vbYellow
          End With
        End With
      End If
    Next
  Next


Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Thanks in advance,
Phil

 

[Eric W]

Re: How to check each delimited value to make sure it meets criteria

The problem is that when you read a range, you get an array object. When you read a single cell, which you're doing because you only have one row, VBA sets data to be a cell object, not an array, causing your problem. Probably the easiest way around that is to change the line above the one you marked to:

data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol[COLOR=#ff0000] + 1[/COLOR]).End(xlUp))

This ensures you have at least 2 cells selected, so you get the array. You do read an entire column of data you're not using, but it really doesn't hurt anything.

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

Ok, I tried that Eric, and now I get the same error, but when I hit debug it moves up to your changed line as being flagged.

 

[Eric W]

Re: How to check each delimited value to make sure it meets criteria

What is your Startrow and Startcol? Is there actually anything in it? Is there anything above it?

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

It is a value that represents a cash amount. This is supposed to check to make sure it has both trailing zero's after a decimal place for a value like 1.00 or 1234.00

There are lines before it, but it is essentially all header data that is static and does not get checked in any way.

My raw data can be anywhere from 1 line of data as in this case to several thousand, it varies.

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

Post #22 of this thread has an example of the raw data that I am inputting on the 'raw data' tab. That data is then broken out onto the tab that this code is processed on and then the cells are checked using this code.

 

[Eric W]

Re: How to check each delimited value to make sure it meets criteria

Sorry, try this instead:

Data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol).End(xlUp).Offset(, 1))