I really hope someone can come to my aid with this!
I have a userform with the following on:
I have a text box that the user enters a role name into. (txtRole)
a text box where data is displayed (txtWinner)
and a text box for the percentage (txtScore)
a command button that says GO! (cmdGo)
and a listbox for the results (lstTopTen)
The rolenames are held on worksheet WS1 in column A1 to A50, next to the role names are numbers in column A-K. The numbers correspond to skills related to the role. (for example: Receptionist 2 24 32 44 50)
The skills related to the role are listed on WK1 in columns AA and AB - AA hold 1 2 3 4 etc. and AB holds the name of the skill that is linked to the number - There is no predetermined length to the amount of skills or roles that can be held. (for example: 2 Microsoft office)
Next, I have another worksheet called WK2 - This worksheet holds the names of people in column A, a skill in colmn B and a rating in terms of how well the person can use the skill from 1 (no skill) to 5 (expert) in column C (for example: John Smith Microsoft Office 4) - The name of the person will appear multiple times as they will have different skills.
I'm looking for some VBA code to stick into my command button that will do the following:
When a role is entered into the textbox (txtrole) and the command button (cmdGo) is pressed the code will look in WS1 for the list of numbered skills related to the role entered (Col A for roles and C-K for numbers). It will then search through the list of skills (col AA linked numbers and Col AB for text) to change the numbers into text skills - Finally, it needs to search through the list of people in WS2 (Col A for names, B for text skills and C for rating) and decide which amongst them is best suited to the role - It does this by finding all the people with 4 and 5 ratings in the skills required for the role and once a list is compiled, works out who is best suited by totting up a score - a person with 5's in every skill will be a 100% match (5 skills required, 5x5 = 25, someone with 4's in each skill would get 20 (4x5 etc..)- an 80% fit to the role. Once a 'winner' has been calculated I would like the winners name to appear on the form in a textbox (txtWinner) with their score (txtScore) - Also, if possible, I would like the top 10 candidates to appear in a list box (lstTopTen)
I would really appreciate some VBA skills on this as this has me absoloutely stumped... I'm not even sure if it's possible!
I've tried to make this as descriptive as possible if you need any more info - Please ask!
Please help!
Fruitbowl.
I have a userform with the following on:
I have a text box that the user enters a role name into. (txtRole)
a text box where data is displayed (txtWinner)
and a text box for the percentage (txtScore)
a command button that says GO! (cmdGo)
and a listbox for the results (lstTopTen)
The rolenames are held on worksheet WS1 in column A1 to A50, next to the role names are numbers in column A-K. The numbers correspond to skills related to the role. (for example: Receptionist 2 24 32 44 50)
The skills related to the role are listed on WK1 in columns AA and AB - AA hold 1 2 3 4 etc. and AB holds the name of the skill that is linked to the number - There is no predetermined length to the amount of skills or roles that can be held. (for example: 2 Microsoft office)
Next, I have another worksheet called WK2 - This worksheet holds the names of people in column A, a skill in colmn B and a rating in terms of how well the person can use the skill from 1 (no skill) to 5 (expert) in column C (for example: John Smith Microsoft Office 4) - The name of the person will appear multiple times as they will have different skills.
I'm looking for some VBA code to stick into my command button that will do the following:
When a role is entered into the textbox (txtrole) and the command button (cmdGo) is pressed the code will look in WS1 for the list of numbered skills related to the role entered (Col A for roles and C-K for numbers). It will then search through the list of skills (col AA linked numbers and Col AB for text) to change the numbers into text skills - Finally, it needs to search through the list of people in WS2 (Col A for names, B for text skills and C for rating) and decide which amongst them is best suited to the role - It does this by finding all the people with 4 and 5 ratings in the skills required for the role and once a list is compiled, works out who is best suited by totting up a score - a person with 5's in every skill will be a 100% match (5 skills required, 5x5 = 25, someone with 4's in each skill would get 20 (4x5 etc..)- an 80% fit to the role. Once a 'winner' has been calculated I would like the winners name to appear on the form in a textbox (txtWinner) with their score (txtScore) - Also, if possible, I would like the top 10 candidates to appear in a list box (lstTopTen)
I would really appreciate some VBA skills on this as this has me absoloutely stumped... I'm not even sure if it's possible!
I've tried to make this as descriptive as possible if you need any more info - Please ask!
Please help!
Fruitbowl.
