Random - array

[Mr. Alex]

Greetings,

I need to rename about 900 files from their current file names to random numbers that cannot be duplicate.

I'm sure i need an array for this, but i do not know how to write one.

I am going to do a .bat file in excel using the dir -> file dump option in windows to get a list of my files, load the text file into excel and using the concatenate function, i can add "rename" and the random number to it.

so in the end it would look something like;

rename filename1.mp3 1204.mp3
rename filename1.mp3 53.mp3

and so on...

My dvd player wont play mp3's random, so i'll just randomize the file names (;

So, any help on getting the random numbers, between 1-2000, no duplicates would be greatly appreciated.

Thanks

Mr. Alex

 

[TommyGun]

Alright, I know I am opening myself to abuse here because this method is probably not the easiest or the best. Also, it is very lightly tested, but it's a rough draft.

These two functions together should create a random array. It can be a unique random array, or a duplicated random array. Here are the two functions: <span style="color: darkblue; font-family: courier">GENERATERANDOMARRAY</span> and <span style="color: darkblue; font-family: courier">GENERATERANDOM</span>

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> GenerateRandomArray(lowvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, highvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, quantity <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> unique <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> currentCount
    <SPAN style="color:#00007F">Dim</SPAN> tmpArry() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> tmpColl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> quantity <= 0 <SPAN style="color:#00007F">Then</SPAN> GenerateRandomArray = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef): <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
  
    <SPAN style="color:#00007F">ReDim</SPAN> tmpArry(quantity - 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> currentCount < quantity
        <SPAN style="color:#00007F">Dim</SPAN> tmpRan
        
        <SPAN style="color:#00007F">If</SPAN> unique <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
            <SPAN style="color:#00007F">Do</SPAN>
                tmpRan = GenerateRandom(lowvalue, highvalue)
                Err.Clear
                tmpColl.Add Item:=tmpRan, key:=CStr(tmpRan)
            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Err.Number = 0
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">Else</SPAN>
            tmpRan = GenerateRandom(lowvalue, highvalue)
            tmpArry(currentCount) = tmpRan
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
        currentCount = currentCount + 1
    <SPAN style="color:#00007F">Loop</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> unique <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> quantity
            tmpArry(i - 1) = tmpColl(i)
        <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    GenerateRandomArray = tmpArry
    
    <SPAN style="color:#00007F">Set</SPAN> tmpColl = <SPAN style="color:#00007F">Nothing</SPAN>
    Erase tmpArry
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Function</SPAN> GenerateRandom(lowvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, highvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> highvalue <= lowvalue <SPAN style="color:#00007F">Then</SPAN> GenerateRandom = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrNA): <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    
    Randomize
    
    GenerateRandom = Int((highvalue - lowvalue + 1) * Rnd + lowvalue)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Also, here is a short example of how to use these in a procedure.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ListRandomArray()
    <SPAN style="color:#00007F">Dim</SPAN> RandomArry
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    RandomArry = GenerateRandomArray(1, 2000, 900, <SPAN style="color:#00007F">False</SPAN>)
    
    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(RandomArry) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(RandomArry)
        ActiveCell = RandomArry(i)
        ActiveCell.Offset(1, 0).Activate
    <SPAN style="color:#00007F">Next</SPAN>
    
    Erase RandomArry
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>