Posts by Miranda

Re: Monte Carlo simulation - lognormal distribution & jumps

Hi,

Not sure if this is any help, but there's an excellent add-in available for Monte-Carlo analysis and suchlike at http://www.cse.csiro.au/poptools/

Cheers,

Miranda

I never would have figured that one out myself....

Thank you, Derk!

Miranda

Hi Everybody,

I'm trying to write a macro (see code below) that repeatedly -say 10 times- generates a set of random numbers and calculates the average of this set. I then want to output these 10 averages to the active worksheet. I've tried to do this using a for-next loop, that first generates the random numbers and calculates the average and then stores this average in an array. However, when the array is transferred to the worksheet, I get the first average 10 times over. When the array is printed to the immediate window, I do get 10 different averages. I don't get it! If somebody can point me in the right direction, it would be greatly appreciated.

Thanks,

Miranda

Sub macro1()
Dim AvRndArray() As Single
Dim nRndNum As Long
Dim nRepeats As Long
Dim Output As Range
Dim Total As Single
Dim i As Long, j As Long


nRndNum = Val(InputBox("How many random numbers per set?"))
nRepeats = Val(InputBox("How many sets?"))


ReDim AvRndArray(1 To nRepeats)


Set Output = ActiveCell.Range(Cells(1, 1), Cells(nRepeats, 1))


For i = 1 To nRepeats
    For j = 1 To nRndNum
        Total = Total + Rnd()
    Next j
    AvRndArray(i) = Total / nRndNum
    Debug.Print AvRndArray(i)
     'This works, different averages are printed in the immediate window
    Total = 0
Next i


Output.Value = AvRndArray
'This doesn't work, I get the first average nRepeats times


End Sub

Hi Scott,

I assume you will be using this sheet to answer questions like: which machines stayed in the warehouse for longer than 2 weeks, which item is in location x, and the like. If that is the case and/or you expect to have many items in your list, then instead of excel, consider using a database program like Access.

In the meanwhile, if you want to sort your entries, just click somewhere in your data, select data-->sort from the menu, and select the column on which you want to sort. From your question, I gather that would be the first one. Excel understands that data in one row belong together, so if it moves the entry in the first column, the entries in the other columns that go with it will also be moved.

Also handy if you want to search through your data is the autofilter option, which you can activate with data-->filter-->autofilter. Especially the Custom.. options is quite usefull.

Hope that helps & good luck with the transport business

Miranda

Hi,

See what happens when you dim your variables as long instead of integer? Integer can only hold values that don't exceed 32.767, anything bigger than that results in an overflow error.

Miranda

Hi Steve,

Yes, that's possible. In the Save As dialog box, click the tools button and then select general options. There, you can specify a password.

Miranda

Hi,

I replaced windows(fn).activate with workbooks(fn).activate and now it works like a bell. Don't know why workbooks are better than windows, but it does the job, so I'm happy. Thanks again!

Blissfully opening numerous textfiles,

Miranda

Hi JF,

Thanks a lot for all the help.

Just quickly ran your code. Don't understand why -seeing as it worked for you- but it only opens and copies the first textfile in the folder and then gives a "subscript out of range error". I've accumulated a large collection of those over the last few days, they're my favorite error message.....
It looks like fn only holds one filename? I've just tried to declare fn as an array, which (probably obviously) didn't work. Will try some more & let you know.

Thanks again,

Miranda

Hi JF,

I was going to open them seperately and then copy them into one sheet. If it is possible to plunk the lot in one sheet, that would save me a lot of work.

M.

Hello everybody,

I've a problem that's probably quite easy to solve, but I've been tinkering for a while now and haven't managed yet...
I'm trying to write a macro that will allow me to import a large number of textfiles into excel. So far I've come up with the following:

Sub OpenTextFiles()
Dim SelectTxt As String


SelectTxt = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
"Select textfiles")
    If SelectTxt = "False" Then Exit Sub
     Workbooks.OpenText SelectTxt
End Sub

With this, however, I can only open one file at a time, which is only marginally better than using the import wizard. I can select multiple files in the open file dialog if I set multiselect to true in GetOpenFilename, but then the macro doesn't work anymore. Any suggestions as to how I should fix this?

Thanx in advance,

Miranda