Generate Random Numbers when values detected under other columns

Hello All,

Can someone help me in generating auto random numbers (thorugh VBA or nesting functions) under Column A when values are populated under column B & Column C. Please find the screenshot attached for your reference, we want to generate auto numbers for Column A2.

Capture 1.JPG

Thanks,
Abbat

.
Paste this in A2 : =IF(NOT(ISBLANK(B2)),RAND()*1000000,"")

Thanks but everytime, i generate the new number i.e. A3 then A2 changes, if I generate A4 then A3 & A2 changes. It would be a great help if i can only generate positive integer only without changing the previously generated number.

.
This method will require a Command Button on the sheet to activate the following macro.
Also, you will need to select the blank cell in Col A where you want the random number prior to clicking the button.

Please Login or Register  to view this content.

Thanks for your response but I like your previous solution better.

I am utilizing following function to randomly generate positive integer =IF(NOT(ISBLANK(B2)),RANDBETWEEN(15,1506009),"").

The recalculation issue can be solved through the following VBA code for RAND() numbers but can you help me in making it for RANDBETWEEN numbers since its not working properly.

Please Login or Register  to view this content.

Capture_RAND VBA Code.JPG

Thanks

.
There are two ways you can prevent the random numbers from changing when you enter ANOTHER random number or data somewhere in the sheet.

# 1:

Go to Excel Options / Formulas / Calculation Options .. then change the setting to : Manual

# 2:

You can assign one cell in the sheet to accept the random number. A cell that won't be utilized for anything else.
Then you can copy the VALUE of that cell to the cell in Col A where you want the random number to reside. Since
the cell in Col A doesn't have the RANDOM formula but ony the random number value, it will not change when you enter
data elsewhere.

Otherwise, you will need to use the macro approach.

#1: the formulas dont work if we change the setting to manual.

#2: then we cannot automate the random number creation through user forms which is the end objective.

While if we can define our own RANDBETWEEN function which works as equivalent to function defined in excel, it will stop it from recalculating.

Thanks again for all the help.