Hi Excel-wizards,
I am stuck with writing VBA code to make randomly entered values into a month sheet unique per working week. So there should not be any duplicate values in the date ranges between weekends.
I attached the relevant workbook. I would be very grateful if anyone could help me out further.
I refer to a similar forum post where I did not get any answers.
The workbook: TEST month sheets with unique random values.xlsm
Actually, the idea is simple: in order for us to help you make a macro right, you can't show us a macro that does the wrong thing...
Upload your workbook again and -by hand- complete two or three sheets with the expected result and explain "something", for example:
- Why ranges of 15 rows?
- Why information in row 4 and nothing else?
- Why those codes and not others?
Hi Beyond,
To answer your questions:
- the expected result: to have only unique random values per working week (also when the weekdays continue in the next month), and if possible to have the random values appear maximum twice a month (see attached image Attachment 806447 which executes the current deficient code that I would like to fix but I don't know how - the cells highlighted in red are unwanted duplicates per week)
- ranges of 15 rows: because in a later stage, I will have to fill all the rows with other random values
- why information in row 4 only: in a later stage, there will be information in all the rows
- why those codes and not others: this is the only code I know but I am open to other codes, which would probably work more efficiently
I hope this answers your questions.
Kind regards
I really don't understand what you need...
But if you are patient you will see that -perhaps- some Forum participant will be able to give you a hand.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Does this site deliver a better service ???
Please Login or Register to view this content.
Last edited by bsalv; 11-24-2022 at 04:42 PM.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Hi bsalv,
I tried your workbook and I get this result Attachment 806771.
As you can see, it fills out values in too many rows. Only the cells in the workday ranges of row 4 of every newly created month sheet should be populated.
In row 4, the values are also returned 4 to 5 times while the maximum number of shorts values per month should be only 2 occurrences.
It also does not use all the "shorts" that contain an x in the group 3 column from the table (in this example, all the shorts have an x in group 3):
Your code does however correctly populate unique random "shorts" values when a workday week is cut in half over two adjacent months.Please Login or Register to view this content.
Do you (or @ beyond Excel) know how to adapt the code to make each random "short" value (having an "x" in the group 3 table column of the list sheet) only appear maximum twice per month in row 4 of every month sheet created?
Hello Retroshift. I'll give you two very good suggestions:
a) None of the images that you attached to these posts can be seen: something went wrong when uploading them to the Forum!
I suggest, then, that you upload images the same way you attach files: That works fine!
b) You have presented your concern very badly (both in this Forum and in the other) and that is why it is difficult to resolve your question.
I suggest, then, that you prepare a workbook with two sheets: a) in the first sheet, show us what you have, b) and in the second sheet, show us what the expected result is.
The more lines of data you show us (solved by hand, of course) it will be easier to help you.
And by the way, be sure to write on that second sheet what type of calculation or consideration you have applied.
Note:
And hurry up because the World Cup matches in Qatar are starting
hello,
i can't open your file, "bad attachment", you'd better add it in the advanced options and the paperclip.
In the sheet "List" columns K:R is the solution for all the groups and in T:AA the numbers.
In previous version, i added all the groups, so now you just want 1, okay, done.
But i don't understand 2 occurrences per month
@ bsalv: VBE gives an error on "CreateObject("system.collections.arrayList")" of your script; and I also thought arrays or the dictionary are more user-friendly than collections?
@ beyond Excel: my bad for the inaccessible images, I did not know. So as you asked, I added the workbook with detailed explanations of what the purpose of the workbook macro is
TEST month sheets with unique random values - Version II.xlsm
@retroshift
the first 2 lines of the module were
So there is a specific reference needed.Please Login or Register to view this content.
With an SCA, there is a sort-function.
the sca is replaced by an array
with the other restraints
Hi bsalv, thanks for your efforts but your workbook does not seem to do the job as explained in the workbook of post #9.
It does not create monthsheets with random values in row 4. Your macro also asks to specify a group, but a group should not be specified because the autofilter code selects the eligible shorts values containing an "x" in the table column of group 3:
Moreover, your script returns 7(?) shorts values that contain duplicates(!) and do not match the eligible shorts values containing an "x" in the table column of group 3.Please Login or Register to view this content.
May I ask you to have a look at the workbook of post #9 which contains the goal of the project.
that part that creates the sheets was blocked with a simple "END" and my macro made all the groups instead of just one.
If you say duplicates, where is the error ?
The script looks very technical to me but it seems to do the trick now, bsalv.
Just to be sure: both 'test tables' (list with days and list with min/max) on the list sheet can be removed/deleted without the code being changed, right?
Another question: where in your code should I put a range of holiday dates that behave like weekend days (i.e. cannot contain any random values)?
I removed both tables and the line in the macro.
For your holidays, i added a table TBL_holidays in sheet "list" but you can move that table to another sheet/place.
Just for the test, every 5th of the month is a holiday.
The weekend and holidays are treated in this lineTechnical ? It was easier to create an array for a whole year in 1 loop then doing it in 3 loops, one for each month, week, day.Please Login or Register to view this content.
Then is tricky to copy and paste the right part of that array of each month to each new worksheet.
Last edited by bsalv; 11-28-2022 at 10:27 AM.
Thanks bsalv. Nice to see some VBA wizards in Belgium ;-)
@bsalv: If I want to run a second autofilter combination of shorts, in order to populate rows 5 and 6 of the new sheets, would the following code work or are there shorter ways?
Please Login or Register to view this content.
i'll do it tomorrow, but it 'll be very similar to my solution #12
in the orange range, the lettertype is "WEBDINGS", so a letter "a" 'll look like a "vinkje".
Now you choose what groups you want to use by adding such a letter "a" in that orange range. Now group 1, 3, 5 and 7 are selected.
You don't have to filter anymore, the shorts are those with no "x" in that group and the short <>""
Hi bsalv, I tried your workbook. The orange range is not an option since the table is not manually editable. The VBA code should be able to read the table on its own without human interference.
Also, the starting point is the combinations of the x's per column. My example in post #18 should be understood as follows:
- first autofilter combination: the short column only contains the short values after filtering the x's from column group 1 and column group 3: which only leaves the values "AS, HB, PA, ST, VR" in this case - So the values with an x should populate row 4 of the new sheets
- second autofilter combination: the short column only contains the short values after filtering the x's from column group 3 and column group 4: which only leaves the values "AS, AB, GN, DE, LQ" in this case - So the values with an x should populate rows 5 and 6 of the new sheets
(- other possible autofilter combinations where also the maximum number of occurrences can be modified in the code)
there is a line in the macro like this, so it's like you asked and the 3rd is an extraBut now there should be an x in both columns ?!?Please Login or Register to view this content.
if so, change this line 5 lines lower
Please Login or Register to view this content.
Last edited by bsalv; 11-29-2022 at 10:29 AM.
Could I also replace "Array(1, 3)" and "Array(3, 4)" by the names of column header strings or does the code need to be rewritten then? E.g.: "Array("Control", "Overwork")" and "Array("Overwork", "Review")".
I wonder how you got so knowledgeable with VBA writing. I would like to expand my knowledge too, because this project includes writing code for the remaining rows (including different maximum criteria), but I would not want to overstretch your helpfulness.
Yes you can, so if it's a string, you're dealing with the headers, if it's numeric it's the columnnumber+1.
I used those cWeek and cMonth as counters, your min and max 'll have to do something similar.
I suppose you'll start with the same person can't be scheduled twice on the same day ???
That knowledge is "een uit de hand gelopen hobby", i was a former PLC-programmer and now i use those skills in another surrounding.
Wonderful. So if my listobject table is actually larger than 8 columns (let's say 15 columns in total) and I want to start from the shorts in column 3, I could change "ReDim aShorts(365, 1 To 8)" to "ReDim aShorts(365, 3 To 15)" or am I mistaken?
Your presumption is correct.
What I would like to do for the rows 4, 5 and 6 on the new sheets is the following:
- row 4: random shorts values (filtered from two table columns 2 and 3; max once a week; max twice a month)
- row 5: random shorts values (filtered from two table columns 2 and 4)
- row 6: random shorts values (filtered from two table columns 2 and 4)
- no same shorts values on the same day for the combined rows 4, 5, 6
- no same shorts values on an adjacent day in the same working week for the combined rows 4, 5, 6
- shorts values can only occur two to four times a month for all the combined rows 4, 5 and 6, depending on their work regime in table column 5 (displayed as percentages in column 5):
if short works 0-50% (= from not working to halftime working) then max two occurrences/month; if short works 51-75% then max three occurrences/month; if short works 76-100% then max four occurrences/month
You think this is possible?
i'm stuck at the last condition
I have to think another way
Last part of the macro, with the creation of the new sheets isnt done, that a minor priority for the moment.
this is custom made and need perhaps some more finetuning.
Let's first discuss about the result, later the method.
In a 3rd sheet, there are pivottables to show that the max values are not overruled.
Not sure if I interpret all the data correctly. I think it would be more clear to me when the values are displayed on the newly created sheets in the destined rows.
Maybe we could add some extra short values because now, there are many blanks on certain days. I also notice duplicates on adjacent days for row 4 (= comb2 column?), and some duplicates for rows 4,5,6 on the same day. That should be avoided.
In the attached image, I gave an example of unallowed (highlighted in red) duplicates in a previous, same or next cell of rows 4,5,6.
When it comes to the work regime condition, it applies to all the shorts in rows 4, 5 and 6. Not only to row 6 shorts. So a short value with for example a 50% regime, can only be entered twice in the total of rows 4,5 and 6 for the entire month range.
As a suggestion that might be of help, the regime percentage column from the table could already be integrated in each array combination to more easily set min and max of short values occurrences for the entire month range with rows 4,5,6 of the new sheets?
Some additional information:
- the "days" column cannot be integrated in the final table as there is no sheet space left; it could be integrated in the vba script
- the maximum of 2 occurrences for a work regime percentage of 0-50% should also be the minimum: no short value can work less than twice a month (nor more than 4 times a month)
- could the column "shorts" also be set up based on a string?; if columns are added to the left of the table, the short column loses its starting position in the array; which is to be avoided. If the column looks for the string header, it keeps track of the column.
If anything is unclear about my clarifications, just let me know. This is complicated matter.
adjacent days was only for row 6, now it's for every row ?
what other restrictions are combined ???
in attachment, no more adjacent cells for rows 4-6.
everything is in the sheet "whole year"
Last edited by bsalv; 11-30-2022 at 03:39 PM.
The restrictions for the combined rows 4,5,6 are these:
I took a screen capture (attached image) of the month of January of the Whole Year:- no same shorts values on the same day for the combined rows 4, 5, 6
- no same shorts values on an adjacent day in the same working week for the combined rows 4, 5, 6
- shorts values can only occur two to four times a month for all the combined rows 4, 5 and 6, depending on their work regime in table column 5 (displayed as percentages in column 5): if short works 0-50% (= from not working to halftime working) then max two occurrences/month; if short works 51-75% then max three occurrences/month; if short works 76-100% then max four occurrences/month
- it looks good regarding duplicates in adjacent or same columns for the combined rows 4,5,6: there are no duplicates; also not when the workday week is cut between two months
- it looks good regarding the max once a week, max twice a month shorts in row 4
- short "AB" now appears 11 times in the combined rows 4,5,6; but it can appear minimum two and maximum four times a month depending on the working regime => according to AB's regime (0,85) it can actually only appear 3 times a month
- short "DE" now appears 8 times in the combined rows 4,5,6; but it can appear minimum two and maximum four times a month depending on the working regime => according to DE's regime (0,75) it can actually only appear 3 times a month
Therefore, there are 8 AB's and 5 DE's per month more than actually allowed.
I hope the conditions and limitations are clear. I know they can be confusing since there are many parameters to take into account.
next version
Hi bsalv, the progress takes a while and I don't know about the code so far, but the results look fine, apart from one thing:
would it be possible to use the maximum of month occurrences for a short, if there are still some empty cells in the rows 4,5,6 that need to be filled? Now there sometimes are only 3 occurrences of the same short with a 100% (4 times max) working regime, while there could be 4 occurrences since there are still empty cells left.
As regards the most recent code itself:
- is there still a line where you can adapt the number of month occurrences of a short for row 4?
- could the starting column in the array be found on the basis of its string headername "shorts"? (because the place of this main column shorts can change depending on new columns and the use of the string name would be easier to find/read in the code)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks