mercredi 23 septembre 2015

Days off array of CheckBox True/False

What I currently have is an array of managers with their own rows, and a check box matching up to a day of the week as the column title in row 1. The check boxes are linked to other cells where they give a value of true/false with the coinciding days at the top of each column.

Column A is the managers Unique Identifier (the value to lookup),
Column B is the managers name (doesn't need to be used for anything in this piece of code),
Columns C-I are Sunday-Saturday check boxes,
Column J is blank,
Columns K-Q are the corresponding true/false values for Sunday-Saturday.

First manager and values start on row 2. We'll call that sheet "Days Off".

On another sheet, I have a list of agents and their managers Unique identifier, along with some empty spaces for days off (each manager can have up to three days off per week so there are three currently blank columns dedicated to this (Columns D-F). Column C is where the managers Unique Identifier is that I need to look up on the "Days Off" Sheet in Column A. We'll call this the "Report" sheet.

What I would like to do is have a piece of code that looks for the value from column C on the "Report" Sheet, finds a match in Column A on the "Days Off" Sheet, then finds the "True" Values along that row and returns the Day name at the top of the Column in Column D, E, and F on the "Report" sheet. If there are No days off (All False), the fields should be left blank, and if there are only two days off the cell in Column F should be left blank. The first "True" Value in the row should return to Column D, the Second to Column E, the third to Column F. The values should start filling in on Row 6 and end on the last row with data in column C.

I am lost as to how to accomplish this, my VB is weak, so I am hoping someone here can help.




Aucun commentaire:

Enregistrer un commentaire