I am having great problems with a VBA script in Excel.
The point of the script is to take a range of cells and randomly spread them over five columns (the script only ever spreads the range over a single column at a time). I have decided to achieve that by choosing a random cell, cutting it to the new destination, sorting the range and redefining it by referencing the first cell and using "End xlDown" to avoid huge potential loops over empty cells.
However I had problems with the "Sort" method. According to MSDN it should be able to be used with no arguments, causing it to sort defaultly, however I was unable to get it running with no arguments, so I gave it explicit cell references to sort by, everything sort of worked - sometimes it would work, other times it would throw a "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank." error. And, of course, the cell being sorted by is within the data being sorted and it does work, when it is empty and I manually sort by it.
In addition I started receiving the "Object required" error.
This is the code that causes the problems:
Set Workspace = Range("A1:H15")
Set exe = Workspace.Find("Exe").Offset(1, 0)
Set Copy = Workspace.Find("Copy").Offset(1, 0)
Set Copy_Range = Range(Copy, Copy.End(xlDown))
For i = 1 To Upr_per_Day
Copy_Range.Sort key1:=Range("C3")
If Copy_Range.Count < 3 Then
Set Copy_Range = Copy
Else
Set Copy_Range = Range(Copy, Copy.End(xlDown))
End If
Randomize
Random = Int((Copy_Range.Count * Rnd) + 1)
Copy_Range.Cells(Random, 1).Cut (exe.Offset(i - 1, 0))
Next
"Upr_per_Day" is a defined integer with a value assigned. The cycle runs fine the first time, but then "Copy_Range.Cells(Random, 1).Cut (exe.Offset(i - 1, 0))" fails the second time with the aforementioned error.
EDIT:
After the helpful tip from Norie, I discovered the root of the problem of the "Object required" error as being the "Offset(i - 1, 0)" property. It, for some reason, causes the "exe" range to be undefined after the first iteration of the loop (which runs fine).
After some fiddling, I have come to the conclusion that it just doesn't like the "Offset(0, 0)" expression. Setting it to "Offset(i - 1, 0)" or "Offset(0, 0)" causes it to work fine the first iteration, but the second it no longer exists.
Therefore currently I have two problems:
1. I do not know why the code does not like the expression "Offset(0, 0)" and would like to find out.
2. The "Sort" method sometimes fails unexpectedly with the error "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.
Attached you can find the workbook, containing the macro.
Book1.xlsm
Bookmarks