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

Okay - see if this works (read the inline comments for my train of thought):

dim x as double' round the cell value to three decimal placesx = round(cell.Value, 3)' x - int(x) gives you the decimal portionselect 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 nothingEnd Select

<<
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.

Aha! There's a floating point arithmetic error creeping in on the line that subtracts out the integer portion... so we need to round that expression to 3 decimal points too.

I've modified it below...

dim x as double' round the cell value to three decimal placesx = 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 nothingEnd Select

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Actually, the two rounds might be overkill. You could probably do just this, only rounding after extracting the decimal portion:

dim x as double' set x to hold the cell valuex = cell.Value' x - int(x) gives you the decimal portion --- *** we need to round this to 3 decimal placesselect 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 nothingEnd Select

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…