Balajibenz
Board Regular
- Joined
- Nov 18, 2020
- Messages
- 80
- Office Version
- 2013
- Platform
- Windows
Hi, Can someone help me with the below requirement.
I have data as below in the sheet1 of workbook1.
It basically has skills, Resource names and their allocation based on the date. In reality the sheet has data for around 200 resources.
I am looking for a VBA code that gives the result as available resources based on the search criteria
In the sheet2 of the same workbook i need below things to be done.
First I will explain how to read data in first sheet, it basically has skills, Resource and project names they are allocated to.
there will be color coding for each project but we should look for blank , grey and red colored cells.
Blank - Resource is available for the particular dates
Grey(RGB(128,128,128) and Hex - #808080) - Resource is tentatively booked
Red(RGB(255,0,0) and Hex - #FF0000) - Vacation
Here is my requirement
1. In Sheet2 I need to have a search criteria where in Skill field must be a dropdown which should have list of unique skills from Column A of sheet1
2. Start and end date column should have a option to select the dates
3. Man days column will be a manual entry
4. Let us say I have selected a Skill "A" and start and end dates are 01-Aug-2021 to 01-Sep-2021 and once i click on the find it should give me the result by looking in to the resource allocation in sheet 1 with information as resource name, Available start date and days he is available within the start and end date(Need to exclude weekends and Vacation which are red colored ones) and the Project they will be working before the requested start date.
5. In case of there are multiple resources available then they should be listed in terms of first available start date when compared to the start date given in the search
6. This scenario should also consider Grey colored periods and in our results we highlight the name of the resource with the same Grey color indicating that resource is tentatively booked.
7. Search can also be done using the skill alone without entering any dates and in this case the results should be from the day of search + 6 months
8. User can also enter Skill and start date alone and in this case results must be published for Start date + 6 Months.
Thank you in advance.
I have data as below in the sheet1 of workbook1.
| Team | Region | Employee Name | Current Manager | TS/GS | 26-Jul | 27-Jul | 28-Jul | 29-Jul | 30-Jul | 31-Jul | 01-Aug | 02-Aug | 03-Aug | 04-Aug | 05-Aug | 06-Aug | 07-Aug | 08-Aug | 09-Aug | 10-Aug | 11-Aug | 12-Aug | 13-Aug |
| C | XYrYvYnYn XhYnmugYm | ||||||||||||||||||||||
| C | HYrXh YgrYwYl | ||||||||||||||||||||||
| B | XX YYY | XNZ & SX | XNZ & SX | ||||||||||||||||||||
| B | KYrthick XYkthivel | X - XLH - Mulesoft IntX UM | NX - XXlXxy - SDS (UXT, LXX InstXll, Xo Live & Support) | NX - PM - WXllet/WCM Xo Live | NX - TeXm mXnXXement / NX - MXp Conversion PlXnninX / NX - Process Improvement | VXcXtion | X - XLH - Mulesoft IntX UM | NX - XXlXxy - SDS (UXT, LXX InstXll, Xo Live & Support) | NX - PM - WXllet | NX - TeXm mXnXXement / NX - MXp Conversion PlXnninX / NX - Process Improvement | X - XLH - Mulesoft IntX UM | NX - XXlXxy - SDS (UXT, LXX InstXll, Xo Live & Support) | NX - PM - WXllet | NX - TeXm mXnXXement / NX - MXp Conversion PlXnninX / NX - Process Improvement | |||||||||
| B | KiXhore KumYr KYppuri | X - XLH - Mulesoft IntX UM | X - XLP - Xo Live | X - XLH - Mulesoft IntX UM | X - Skycity - Multiprotocol | VXcXtion | |||||||||||||||||
| D | XrinivYXYn MYni | X - XLH - XooXle Sheets | X - XLH - XooXle Sheets | X - XLH - XooXle Sheets | |||||||||||||||||||
| D | YnYXuyYmmY RYnjith KumYr | NX - TXXleu TrXininX | X - XLP - Xo Live | X - Peermont - XI InteXrXtion | NX - XS - Tech Support | ||||||||||||||||||
| D | Xindhu VenkYteXh | XI SRs L2 Support | NX - TXXleu TrXininX | XI SRs L2 Support | XI SRs L2 Support | ||||||||||||||||||
| C | PrYdeep PrYbhu | XI SRs L2 Support / VenetiXn EXS ShXdow | NX - TXXleu TrXininX | X - XLH - WXllet CRS | X - XLH - WXllet CRS | ||||||||||||||||||
| A | RYkeXh MYrwYhY | XsiX Projects | XsiX Projects | XsiX Projects | |||||||||||||||||||
| A | XrihYri RYmYn | X - Peermont - HUX | X - XX R6 - JDT EnhXncement | NX - PCS / NX - TeXm MXnXXement | X - Peermont - HUX | X - XX R6 - JDT EnhXncement | NX - PCS / NX - TeXm MXnXXement | X - Peermont - HUX | |||||||||||||||
| A | XhubhYm VermY | X - XLP - Xo Live | NX - SJM - HUX enhXncements | X - Peermont - HUX | X - Peermont - HUX | ||||||||||||||||||
| A | YbinYyY XrirYngYn | X - XX R6 - JDT EnhXncement | X - XLP - Xo Live | NX - XX R6 - JDT EnhXncement | NX - XLP - Post Xo Live Issue | NX - XX R6 - Retrofits | VXcXtion | ||||||||||||||||
| A | XuXhmithY BYbu | X - XLH - WXllet | X - XLH - WXllet | X - XLH - WXllet | |||||||||||||||||||
| X - CXC - LFV MXp | NX - XXlXxy - HUX UXT Cycle 4 | NX - XXlXxy - UXT CMP/CXXE/TV TriXXe | NX - XXlXxy - UXT CMP/CXXE/TV TriXXe | ||||||||||||||||||||
It basically has skills, Resource names and their allocation based on the date. In reality the sheet has data for around 200 resources.
I am looking for a VBA code that gives the result as available resources based on the search criteria
In the sheet2 of the same workbook i need below things to be done.
| Skill | |||
| Tentative Start Date | |||
| Tentative End Date | |||
| Total Man-days | |||
| FIND | |||
| Resource | Available Start Date | Days Available | Current Project |
First I will explain how to read data in first sheet, it basically has skills, Resource and project names they are allocated to.
there will be color coding for each project but we should look for blank , grey and red colored cells.
Blank - Resource is available for the particular dates
Grey(RGB(128,128,128) and Hex - #808080) - Resource is tentatively booked
Red(RGB(255,0,0) and Hex - #FF0000) - Vacation
Here is my requirement
1. In Sheet2 I need to have a search criteria where in Skill field must be a dropdown which should have list of unique skills from Column A of sheet1
2. Start and end date column should have a option to select the dates
3. Man days column will be a manual entry
4. Let us say I have selected a Skill "A" and start and end dates are 01-Aug-2021 to 01-Sep-2021 and once i click on the find it should give me the result by looking in to the resource allocation in sheet 1 with information as resource name, Available start date and days he is available within the start and end date(Need to exclude weekends and Vacation which are red colored ones) and the Project they will be working before the requested start date.
5. In case of there are multiple resources available then they should be listed in terms of first available start date when compared to the start date given in the search
6. This scenario should also consider Grey colored periods and in our results we highlight the name of the resource with the same Grey color indicating that resource is tentatively booked.
7. Search can also be done using the skill alone without entering any dates and in this case the results should be from the day of search + 6 months
8. User can also enter Skill and start date alone and in this case results must be published for Start date + 6 Months.
Thank you in advance.
Last edited:
