Today, when processing the score, a friend needs to change all the cells smaller than 60 to 60 points. Her idea was to replace them with search, which excel really does not support, after reading the large data, I wrote a small VBA program for her. The function is to replace all the cell values in the selected area, of course, conditional replacement, the Code is as follows. The detailed conditions are not explained. I have annotated:
Set the shortcut in the macro and press Ctrl + Shift + F to automatically replace it. Tens of thousands of records have been changed.
Sub setto60 ()
'
'Set the number of cells smaller than 60 to 60.
'Macro is recorded by yangbh. Time:
'Qq: 283522147
'Shortcut: Ctrl + Shift + F
'
Dim ostr as string 'get the content of the current calculated Cell
Dim Osel as range' defines the currently calculated region (cell)
Dim osng as single 'get the single precision value of the current computer Cell
For each Osel in selection
Ostr = cells (Osel. Row, Osel. Column). Value
If ostr <> "" then' empty cells do not operate directly, entering the lower-layer Loop
If isnumeric (ostr) then' cannot be converted to the numeric type, skip this step and enter the lower-level loop.
Osng = csng (ostr)
If osng <60 then' is filled with 60 if less than 60
If osng <> 0 then', if it is equal to 0, it is skipped and enters the lower-layer loop.
Cells (Osel. Row, Osel. Column). value = 60
End if
End if
End if
End if
Next Osel
End sub