Can I use a macro to enter Poisson random numbers?

Hey, I'm sure this has been asked before but I couldn't find it using a quick search so here goes...

I have a spreadsheet which has a column of 100 Poisson-distributed random numbers (lambda=2) which I generated using Data Analysis -> Random Number Generation in Excel. I want to create a macro to put these numbers in for me. When I tried recording one, it didn't work. Is there a way to do it?

I can provide more info if needed! Thanks!

When I recorded a macro, I got this. It should be possible to adapt, no?

Application.Run "ATPVBAEN.XLA!Random", "", 1, 100, 5, , 2

I also got a similar piece of code (I'm using Excel 2007 by the way):

Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$A$1:$A$100"), _
        , 100, 5, , 2

But when I try running the macro, it gives me an error:

Run-time error '1004':

Method 'Run' of object '_Application' failed

How can I fix this?

Copy what the macro recorder throws up:

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$A$1:$A$100"), 1, 100, 5, , 2

Hi Stephen,

When I tried copying the line exactly as you entered it, i.e.

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$A$1:$A$100"), 1, 100, 5, , 2

I got an error which said "ATPVBAEN.XLA!Random" could not be found.

When I changed it to:

Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$A$1:$A$100"), 1, 100, 5, , 2

(with an extra 'M' in the filename), as the macro recorded gave me originally, I got the error that I described above:

Run-time error '1004':

Method 'Run' of object '_Application' failed

Any suggestions?

Are you using Excel 2003? I think the extra M is for 2007. Have you definitely installed the add-in? Tools > Add-Ins.