random number issue

Either use Randomize without an argument, or use Randomize Application.WorksheetFunction.Randbetwwen(1,999999).

That said, worksheet RAND() and RANDBETWEEN() use much better pseudorandom number generators than VBA's Rnd. If I recall correctly, VBA's Rnd uses an 1970s era linear congruential generator. MSFT updated Excel's worksheet RAND() to reflect a few extra decades of numerical programming research in Excel 2003 and more in Excel 2007.

If you need to do serious Monte Carlo simulations, you should be using a real stats package rather than ANY spreadsheet, but if you have to use Excel, you'd actually be much better off using Application.WorksheetFunction.Rand() and consigning VBA's Rnd to the oblivion it deserves.

Thank you hrlngrv,

I found worksheet RAND() better alternative for my Monte Carlo Simulation. Thank you for sharing this valuable knowledge.

Roshan