How to generate random numbers conditionally in Excel 2016: The generated random numbers cannot be the same as other cell values.

In order to facilitate verification here, we try to make the random value as small as possible. If you want a larger random number, you can modify the program.

Suppose that a random number (between 1 and 5) is taken in cell D1. Cells A1 and B1 are cells with fixed values. Of course, the value range is also directly from 1 to 5, otherwise it is meaningless.

To write VBA, you must first open VBA. There is more than one way to open VBA. Here we will use the simplest and fastest way, using shortcut keys. Press "Alt+F11" to open the VBA window. The sub-window on the right side of the VBA window is used for entering code.

Enter the code as follows:

Public Sub btsj()Dim sjd As Range Set sjd = Range("D1")line1: sjd.ClearContents Randomize sjd = Int(Rnd * 5 + 1) If sjd = Range("A1") Or sjd = Range("B1 ") ThenGoTo line1End IfEnd Sub

After completion, you can test run and save. The running icon is the small triangle, which looks like the play icon. Needless to say, the saved icon. A window may pop up after clicking the small triangle icon, then click Run.

For quick verification, we can add a shortcut key to the macro, as shown in the figure. Here we set the shortcut key "Ctrl+s" for it. Now we only need to press Ctrl+s to quickly run the macro/program.

Things to note

Be careful when writing VBA programs. If there is an error message, you need to analyze it rationally and check it patiently.

What are the functions for automatically generating random numbers in Excel 2016?

=IF(RAND()>0.8,90*RAND(),10*RAND()+90) randomly generates numbers from 0 to 100 in a cell range, but numbers less than 90 account for more than 80% of the total

=RAND()

=RANDBETWEEN(a,b) generates a random integer between number a and number b in excel

=a+RAND()*(b-a) Generate a random real number between number a and number b in excel

Summary: The above is all about generating random numbers based on conditions in Excel 2016, and some introductions to automatically generating random numbers. I hope it will be useful to everyone.