Posts by 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.



    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



    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.



    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,


    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,


    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,