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

Well I used this and it isn't doing what I want it to do as it is wiping out my Conditional Formatting I have in place:

Range("B7", "BF6000").ClearFormats

That works in terms of clearing it back to system defaults, which is what I have on the 'PIF Checker' sheet, other than, as I said it wipes out the mass of conditional formatting I have in there.

Is there a way to just set the cells back to either no fill color or a gray (for the 'PIF>Batch' sheet) and default font weight and color?

I have some other stuff going on with work so trying to research this still but being pulled in other directions for the time being. I'll come back to it as I can today though.

Phil

 

[Fennek]

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

Hi,

with your example in A1:

Sub Main
dim Str() as string
Str = split(trim(cells(1,1)), "|")
for i = 0 to ubound(Str)
    if  mid(str(i),len(str(i))-2,1) <> "." then msgbox "mistake"
next i
End Sub

regards

 

[Rick Rothstein]

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

Well I used this and it isn't doing what I want it to do as it is wiping out my Conditional Formatting I have in place:

Range("B7", "BF6000").ClearFormats

Since Conditional Formatting is a "format", it goes away when you ClearFormats. Because the cell and font colors and font boldness were set manually, you will need to reset them manually back to their default values.

 

[philwojo]

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

OK, Thanks I have it figured out, not elegant, but it is at least working.

Here is what I am doing, again not very elegant at all

Range("B7", "BF6000").Interior.Color = vbWhiteRange("B7", "BF6000").Font.Bold = False
Range("B7", "BF6000").Font.Color = vbBlack
Range("B7", "BF6000").Borders.LineStyle = xlContinuous
Range("B7", "BF6000").Borders.Weight = xlThin
Range("B7", "BF6000").Borders.ColorIndex = xlAutomatic
Range("B7", "BF6000").HorizontalAlignment = xlCenter
Range("B7", "BF6000").VerticalAlignment = xlCenter

 

[Rick Rothstein]

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

OK, Thanks I have it figured out, not elegant, but it is at least working.

Here is what I am doing, again not very elegant at all

Range("B7", "BF6000").Interior.Color = vbWhiteRange("B7", "BF6000").Font.Bold = False
Range("B7", "BF6000").Font.Color = vbBlack
Range("B7", "BF6000").Borders.LineStyle = xlContinuous
Range("B7", "BF6000").Borders.Weight = xlThin
Range("B7", "BF6000").Borders.ColorIndex = xlAutomatic
Range("B7", "BF6000").HorizontalAlignment = xlCenter
Range("B7", "BF6000").VerticalAlignment = xlCenter

The only suggestion I would make is for you to become familiar with the With/End With block statements. Whenever you see that you will have a several references to the same object (a range reference in the case above, you can specify that object as the argument for the With statement, and then reference it by simply putting a dot in front of any of its properties or methods that you want to execute, then you close the block structure off with an End With statement. Besides looking cleaner and reducing the amount of typing you need to do, it is actually slightly more efficient as well. Here is what your above code would look like using a With/End With block...

With Range("B7", "BF6000")
  .Interior.Color = vbWhite
  .Font.Bold = False
  .Font.Color = vbBlack
  .Borders.LineStyle = xlContinuous
  .Borders.Weight = xlThin
  .Borders.ColorIndex = xlAutomatic
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
End With

 

[philwojo]

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

Thanks Rick, I knew there was a better way and I've update my code to what you posted.

I had other code that was similar so I updated that and made it work as well.

I appreciate all the feedback and assistance, I really like learning, just never seem to have the time to do it as often as I would like.

 

[philwojo]

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

OK, so I have a slight tweak I need to make to this code and I'm not sure how to do it.

here is the current code:

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


  ' Process cells on "PIF File Checker" sheet
  StartRow = 7
  StartCol = "AD"
  Set ws = Sheets("PIF File Checker")
  Data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol).End(xlUp))
  For r = 1 To UBound(Data)
    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.]*" 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 "PIF>BATCH" sheet
  StartRow = 31
  StartCol = "D"
  Set ws = Sheets("PIF>BATCH")
  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.]*" 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

It is working and doing what I expect, but I now need to add another condition to the check that I wasn't aware of before. I need to also highlight the cells if the dollar value does not have the leading digits in front of the decimal point. Meaning if the value is only $.97 and the zero is not there I need to call that out also.

Oh and if possible can I have it ignore if a value is wrapped in (), parenthesis. This is used to indicate a negative value and is valid. So a negative value would be represented as (312.56). The other rules apply for negatives though, the must also have the leading and trailing digits before and after the decimal point.

Thanks,
Phil

 

[philwojo]

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

Anyone have any suggestions on how to update this code?

 

[Rick Rothstein]

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

It is working and doing what I expect, but I now need to add another condition to the check that I wasn't aware of before. I need to also highlight the cells if the dollar value does not have the leading digits in front of the decimal point. Meaning if the value is only $.97 and the zero is not there I need to call that out also.

Add (using the Or operator) another Like comparison to the long list of Or'ed Like comparisons that you currently have...

S(X) Like "*$.#*"

 

[philwojo]

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

Thanks Rick, I knew it would be that, I just didn't know the format to be used.

I tried your suggestion in my code and it doesn't seem to catch missing leading zero's with that code. Here is my new code just so you can see how I added it, but it is running OK, no faults.

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


  ' Process cells on "PIF File Checker" sheet
  StartRow = 7
  StartCol = "AD"
  Set ws = Sheets("PIF File Checker")
  Data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol).End(xlUp))
  For r = 1 To UBound(Data)
    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 S(X) Like "*$.#*" 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 "PIF>BATCH" sheet
  StartRow = 31
  StartCol = "D"
  Set ws = Sheets("PIF>BATCH")
  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 S(X) Like "*$.#*" 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