Forum Discussion
Solved
Runtime error 1004 Application-defined or Object-defined error
I have yet another runtime error; this time it's 1004 instead of 6. Again, this code was originally French and I did not make it myself.
so as you know I originally suggested a pseudo-random formula that uses a seed and that way you could feed the week# or day# or such to the pseudo function and always get the same output, but changing the seed to the new week/day/other will produce a new random number/sequence. The problem is the 'must be occupied' part since that will also change over time so you would have to keep a logging of WHEN units become occupied and vacant.
The other alternative is to use the built in random generator based on current state of the rooms and then copy/past those results (paste values) into the table. Maybe the easiest is to have a column called [Inspection date] and another called [Inspection Update]. under inspection date will ultimately be the actual date of inspection. and I'm sure you have a column to check if occupied (maybe their name or whatever) but for this we will assume a column called [Occupied] that is true or false. Then=LET(r,RANDARRAY(ROWS( [InspectionDate] ))* [Occupied] *( [InspectionDate] =""), t,INDEX(SORT(r,,-1), 14), o,IF(r>=t,TRUE,""), IF( [InspectionDate] ="",o, [InspectionDate] ))so in line 1 it will create a random array or numbers but zero out the unoccupied and previously inspected rows
in line 2 it will find the Nth largest value, in this example the 14th
in line 3 it will blank out all but the top 14 and replace those with TRUE
and the final output (line 4) will be either the prior inspection date or TRUE if the inspection should be done.
being a RAND function the result will keep changing every time you edit anything on the sheet so when you are ready to committ for that week select that column and COPY then select the [InspectionDate] column and goto Paste->Paste Special->Values Only
Since all the prior dates are repeated it will just add the TRUE to the new locations and those TRUE can be replaced with actual inspection dates. Alternatively you could replace the TRUE in line 3 with WEEKNUM(TODAY()) and then each week you run it and it will just show which weeknum those rooms were selected.
Final caution is that even though rooms that were not occupied will get occupied and have the opportunity to get selected throughout the year this way, you can still have an issue at the end of the year if one or more of those remaining 14 rooms become unoccupied at that point of the year.
Here is a copy of the workbook. I'm sure that once you start looking through it you'll realize that there's A LOT wrong with it.
The problem is that NONE of the cells in column A of the Risk Assessment sheet contains "END OF RISKS", so loop never stops until the variable row is greater than the number of rows on the sheet (1048576).
To make it work, column A MUST contain a cell with value "END OF RISKS".
Another point: you have several lines
Clear tab_value()
This should probably be
Erase tab_value()
- I was able to fix this problem but I still have one major problem.
After clicking on the "Create the risk assessment list" button, the page is supposed to automatically change so that I am viewing the Risk Assessment sheet. Not only is it not doing that, but that sheet isn't auto populating the results from the Global Risk Assessment Matrix sheet. Do you know where in my code this error is occurring?The next to last line of the macro column_selection (in Module11) is
End:
This makes the code stop completely. Remove that line, it isn't needed.
- That worked perfectly! Thank you!