In Excel, change all cells smaller than 60 to 60.

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.