The previous while agreed to write a small tool for the classmate. It is necessary to easily realize the four hits in Excel, which is like 5.25, to keep a valid number, the result is 5.2, if it is 5.15 The result is 5.2. That is, if the next bit of the last bit to be retained is 5, you will see the last bit to be retained. If it is an odd number, it will enter 1. If it is an even number, it remains the same, that is, five considerations. This is more used in the experiment.
I thought about it, just saw a similar article a few days ago, I did it. Because it is not simply four, it is necessary to consider it. Considering how easy, make a macro in the way, customize a letter to this macro, click to run.
The following is the main process of processing, where IPOS is the number of digits after the decimal point to be retained. The effective range is an integer between 0-8. Directly override the original data directly. This is relatively simple, look at it.
Public Sub Process (IPOS AS Integer) DIM M AS INTEGER, N AS Integer Dim Rang As Range ON Error ResMe next set rang = Application.Selection if (Rang.Rows.count = 0) and (rang.columns.count = 0) "You have to choose the data you want to handle! Drag and choose it, really stupid!" End if for m = 1 to rang.rows.count 'for each row for n = 1 to rang.columns.count' For each column IF rang.cells (m, n) <> "" "" = getnumber (rang.cells (m, n), IPOS) endNumber (rang.cells (m, n), IPOS) end = Nothing Msgbox "Processing" end SUB
Do a specific conversion tool in this function.
First check the position of the decimal point. If the number of digits after the decimal point is not enough to keep the number of bits, it is like 3.14 to keep 5 digits after the decimal point, then no matter, return the original value directly.
If it is 0, directly intercept all values before the decimal point, return.
If you need rounding processing, take the next bit of the last bit to check, check the value of this bit, if it is greater than 5, the last bit into 1; if less than 5, the last bit is constant; if Is equal to 5, then consider.
If the last bit is an even number, it takes the number of digits that needed, returns. If it is odd, the last bit plus 1, returns.
Comments in the code are comments.
Private Function GetNumber (F AS Double, IPOS AS Integer AS DOUBLER DIM S AS STRING DIM S1 AS STRING S = TRIM (STR (f)) i = I = INSTR (1, S, "." ) 'Location of the decimal point' Check if it needs to process S1 = s if ipos = 0 THEN IF I> 0 THEN S1 = LEFT (S, I) ELSE 'Decimal Number of points after the decimal point. No more than, if you do not need to process if Len (s) - i> ipos Then 'last one <5: went,> 5: carry, = 5: 进 奇 S1 = MID (S, I IPOS 1, 1) K = VAL (S1) IF K <5 Then 'going to S1 = Left (S, I IPOS) ELSE IF K> 5 Then' carry S1 = MID (S, I IPOS, 1) k = VAL S1) K = K 1 S1 = Left (S, I IPOS - 1) TRIM (STR (K)) ELSE 'odd coular S1 = MID (S, I IPOS, 1) k = VAL (S1 IF K MOD 2 = 0 THEN S1 = LEFT (S, I IPOS) ELSE K = K 1 S1 = Left (S, I IPOS - 1) TRIM (STR (K)) End IF 'odd End End IF 'inlet determination End End IF' The last bit of the next bit of the next bit is less than 5 end if 'decimal points, the number of precision to save the END IF' reserved bit greater than 0 getNumber = VAL (S1) ' The data after the process of returning is successfully running by the program under W2K Excel2000.
I don't know how to automatically act efficient to all documents, that is, when opening Excel, it is also valid when new documents. Hope high guidance.
