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

To add a bit of clarity the values would be in Column AD Rows 7:6000 on one sheet and in Row 29 columns D-H on another sheet, both in the same workbook.

 

[Rick Rothstein]

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

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.

We could do what you asked for OR we could simply fix any values that were missing their two decimal values... just let us know which you want. Either way, we need to know where on each of your sheets these value are located at.

EDIT NOTE: I see you posted the columns the data is in, but not the sheet names for each set of columns. We need to know the sheet names too.

 

[philwojo]

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

I don't want to fix them, as the data is being pulled from external data, so it needs to be called out so the external file can be fixed.

 

[Rick Rothstein]

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

I don't want to fix them, as the data is being pulled from external data, so it needs to be called out so the external file can be fixed.

Okay, fine, I was just offering you an alternative I thought you might want to consider.

You listed the ranges on your two sheets to check, but you did not tell us what the sheet name was for each of those ranges... we need to know that piece of information.

 

[philwojo]

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

Sorry Rick, wasn't trying to be short with my answer at all, was just answering why I didn't want to change it, I apologize if it came across any way other than that.

Sheet names are
'PIF File Checker' for the 1st range I gave
'PIF>BATCH' for the 2nd range I gave

Thanks,
Phil

 

[philwojo]

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

Rick any feedback or do you still need more information from me?

 

[Rick Rothstein]

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

Rick any feedback or do you still need more information from me?

Give this macro a try...

[table="width: 500"]
[tr]
	[td]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
  [B][COLOR="#008000"]' Process cells on "PIF File Checker" sheet[/COLOR][/B]
  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
          .EntireColumn.AutoFit
        End With
      End If
    Next
  Next
  [B][COLOR="#008000"]' Process cells on "PIF>BATCH" sheet[/COLOR][/B]
  StartRow = 29
  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
          .EntireRow.AutoFit
        End With
      End If
    Next
  Next
End Sub[/td]
[/tr]
[/table]

 

[philwojo]

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

This appears to be working, the only thing I would like to change is to have it keep the default cell formatting/colors and/or revert back to that if no issues are found. Right now it leave the new fill color and font colors in place.

I am going to research this and see if I can figure it out, I don't need to do it as part of this macro, I just need to do it when I clear the cells before more data is put through the sheet.

Rick, thanks for this, I appreciate the assistance, I know there is now way I would have ever figured this out on my own, this is way above my skill set.

Phil

 

[Rick Rothstein]

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

This appears to be working, the only thing I would like to change is to have it keep the default cell formatting/colors and/or revert back to that if no issues are found. Right now it leave the new fill color and font colors in place.

I am going to research this and see if I can figure it out

If you have trouble working this out on your own, feel free to post back here and someone will try to help you out. If you do end up needing some help, please tell us what the "default cell formatting/colors" for those cells are.