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

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

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …

With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks.
Specify a start-up form through options:
Specify an Autoexec macro:
Us…