With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Hello Experts -

I have the attached code that I need to update and I am not sure how to do it.

I need to change the pieces that look for the .001 or .999.

I need to look for any instance where the cell value rounded to the 3rd decimal place ends in .001 or .999. If it does, then I need to change it end with either .002 or .998.

The number might be a positive or a negative number and may not be rounded at all, it could be .0008954 or something like that.

I have the attached code that I need to update and I am not sure how to do it.

I need to change the pieces that look for the .001 or .999.

I need to look for any instance where the cell value rounded to the 3rd decimal place ends in .001 or .999. If it does, then I need to change it end with either .002 or .998.

The number might be a positive or a negative number and may not be rounded at all, it could be .0008954 or something like that.

```
For Each cell In rgsearch
If IsError(cell.Value) Then
xlws.Range(pbcol & cell.Row & ":" & pecol & cell.Row).ClearContents
End If
If Not IsNumeric(cell.Value) Or (cell.Value = "") Then
'Debug.Print cell.Row
xlws.Range(pbcol & cell.Row & ":" & pecol & cell.Row).ClearContents
End If
If (cell.Value > 0) And (cell.Value <= 0.001) Then
cell.Value = 0.002
End If
If (cell.Value > 100) And (cell.Value <= 100.001) Then
cell.Value = 100.002
End If
If (cell.Value < 0) And (cell.Value >= -0.001) Then
cell.Value = -0.002
End If
If (cell.Value > 99.998) And (cell.Value <= 99.999) Then
cell.Value = 99.998
End If
Next cell
```

Try adding this where you need the formatting done:

```
Dim s as string
s = Right(FormatNumber(cell.Value, 3), 3)
If s = "999" then Cell.Value = "" & Left(cell.Value, instr(1, Cell.Value, ".") & "998"
if s = "001" then Cell.Value = "" & Left(cell.Value, instr(1, Cell.Value, ".") & "002"
```

A specific example is the cell value is 0.000083549. Rounded to the 3rd decimal place, this would be 0.001.

I need to change the cell value completely from 0.000083549 to 0.002.

Or, if the cell value were 100.999000201, then the cell value would need to change to 100.998.

```
dim x as double
' round the cell value to three decimal places
x = round(cell.Value, 3)
' x - int(x) gives you the decimal portion
select case (x - int(x))
' If the decimal portion is .999 update the cell to the int part + .998 decimal
Case .999
cell.Value = int(x) + .998
' If the decimal portion is .001 update the cell to the int part + .002 decimal
Case .001
cell.Value = int(x) + .002
' If it is neither, do nothing. Select case block gives you room for more options
Case Else
' do nothing
End Select
```

Your example doesn't look quite right:

<<

A specific example is the cell value is 0.000083549. Rounded to the 3rd decimal place, this would be 0.001.

>>

0.000083549 rounded to the 3rd decimal is actually zero. Double-check your data and requirements - you'll have to use Round(x, 4) if you are really looking for .0001 for this number.

However, if the cell value rounded to the 3rd place is anything else, say 99.001 or 102.001 or 2.001 or 98.999, it doesn't work.

I've modified it below...

```
dim x as double
' round the cell value to three decimal places
x = round(cell.Value, 3)
' x - int(x) gives you the decimal portion --- *** we need to round this result too.
select case Round((x - int(x)), 3)
' If the decimal portion is .999 update the cell to the int part + .998 decimal
Case .999
cell.Value = int(x) + .998
' If the decimal portion is .001 update the cell to the int part + .002 decimal
Case .001
cell.Value = int(x) + .002
' If it is neither, do nothing. Select case block gives you room for more options
Case Else
' do nothing
End Select
```

The 2nd solution is working, but will try the last solution as well, then I will close this question.

Thank you for your help!

All Courses

From novice to tech pro — start learning today.

Open in new window