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

OK, that fixed the first part, there is a 2nd line that I think needs to be updated as well lower down in that code, here is the line as it is today.

Data = ws.Cells(StartRow, StartCol + 1).Resize(, 5)

I'm not sure how I would apply the OFFSET to that one like you did in the above code. This is on another sheet within my workbook, hoping you can help out with this last bit.

Thanks,
Phil

 

[Eric W]

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

The Resize ensures a range with 5 cells in it, so this shouldn't need to be changed.

 

[philwojo]

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

I'm not disagreeing with you, but I am getting the same error message and it now points to this line of code when I hit debug, so it doesn't like something and again it only does this when I have a single line of data, not multiple lines of data on my Raw Data sheet.

 

[philwojo]

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

Any thoughts on why I am getting this error message on this line now?
Anything I can try to update this so it doesn't happen?

 

[Eric W]

I believe this is your original code snippet:

  StartRow = 31
  StartCol = "D"
  Set ws = Sheets("Output_Vert_5_Entries")
  Data = ws.Cells(StartRow, StartCol).Resize(, 5)

You changed the final line to

Data = ws.Cells(StartRow, StartCol + 1).Resize(, 5)

This will cause an error because StartCol is a letter ("D"), and you can't add 1 to a letter. But as I mentioned before, the original line will not have the same type of error because the Resize ensures that you have a multi-cell range. Just revert to your original line.

 

[philwojo]

See what happens when you get ahead of yourself, thanks Eric, I changed it back and as you said it works as expected. I truly appreciate all the help you and everyone else has provided.