Searching and auto filing using macros in excel with in sheets

Description:

Sheet1 contains Employee details as follows
(Employee Name, Project, Skills, Role, Start Date, End Date)

Sheet2 contains the new modules/projects which need some more man power with these details
(Project, Skills, Role, Start Date, No. of Persons Required, No. of Persons available, Gap, Names Available)

Here is the Sheet1 in excel

Employee name	Project	Skills	Role	Start Date	End Date
Abc	P1	S1	R1	2-Feb-10	15-Oct-10
Def	P2	S2	R2	15-Mar-10	20-Sep-10
Ghi	P3	S2	R3
Jkl	P4	S1	R4	15-Jan-09	20-Dec-09
Mno	P3	S1	R1	15-Mar-10	15-Mar-12
PSR	P6	S2	R2	15-Mar-10	12-Dec-10

   

Sheet2 contains as follows

Project Name	Skills	Role	Start Date	No. Of Persons Required	No. Of Persons Exist	Gap	Names Available
P5	S1	R2	16-Oct-10	5	3	2
P1	S2	R1	2-Feb-10	6	6	0
P6	S1	R1	1-Jan-11	2	1	1
P3	S2	R2	15-Mar-10	4	3	1
P7	S2	R3	21-Aug-10	5	3	2
P6	S2	R2	1-Jan-11	7	3	4

Now, we can write a macro which will fill the names available column with the specified skills and roles

Macro Code:



'Macro Code Starts Here

Sub Find_Gap()
    Dim names As String
    Dim startDate As Date
    Dim skillsVal As String
    Dim roleVal As String
   
    Dim currentCell As Range
   
    'Searching or Finding Value should be in Sheet2
    Sheets("Sheet2").Activate
   
    'Gap column should be in 'G' Column
    Set currentCell = Range("G2")
    currentCell.Select
    names = ""
    'MsgBox (currentCell)
   
    Do
        names = ""
        gapVal = ActiveCell.Value
    If gapVal > 0 Then
        'MsgBox (gapVal)
        'startdate column should be in 'D' Column i.e D-G=-3
        startDate = ActiveCell.Offset(0, -3).Value
       
        'Role column should be in 'C' Column i.e D-G=-4
        roleVal = ActiveCell.Offset(0, -4).Value
       
        'Skills column should be in 'B' Column i.e C-G=-5
        skillsVal = ActiveCell.Offset(0, -5).Value
       
        'MsgBox (startDate & skillsVal & roleVal)
        names = SearchPeople(startDate, skillsVal, roleVal)
    End If
    Sheets("Sheet2").Activate
    'MsgBox (names)
    If names <> "" Then
        currentCell.Offset(0, 1).Value = names
    Else
        currentCell.Offset(0, 1).Value = "-NA-"
    End If
   
    'Name Available Column should be next to Gap 'G' Column
    Set currentCell = currentCell.Offset(1, 0)
   
    currentCell.Select
   
    'Gap column Should be in 'G' Column
    Loop Until IsEmpty(ActiveCell.Offset(0, -6).Value)
   
End Sub




Function SearchPeople(startDate As Date, skillsVal As String, roleVal As String) As String
       
    'Employee Details should be in Sheet1
    Sheets("sheet1").Activate
   
    Dim names As String
    names = ""
   
    'Employee Name should be in 'A' Column
    Range("A2").Select
   
    Do
    'StartDate should be in 'E' Column and EndDate Should be in 'F' Column
        If ActiveCell.Offset(0, 4).Value <> Null Or ActiveCell.Offset(0, 5).Value < startDate Then
            'Skills column should be in 'C' Column and Roles in 'D' Column
            If ActiveCell.Offset(0, 2).Value = skillsVal And ActiveCell.Offset(0, 3).Value = roleVal Then
                If names <> "" Then
                    names = names & "," & ActiveCell.Value
                Else
                    names = ActiveCell.Value
                End If
            End If
        End If
        'MsgBox (names)
       
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Value)
    SearchPeople = names
End Function

'Macro Code Ends Here


When you run the macro, the Names Available column will be filled as follows

Project Name	Skills	Role	Start Date	No. Of Persons Required	No. Of Persons Exist	Gap	Names Available
P5	S1	R2	16-Oct-10	5	3	2	-NA-
P1	S2	R1	2-Feb-10	6	6	0	-NA-
P6	S1	R1	1-Jan-11	2	1	1	Abc
P3	S2	R2	15-Mar-10	4	3	1	-NA-
P7	S2	R3	21-Aug-10	5	3	2	Ghi
P6	S2	R2	1-Jan-11	7	3	4	Def,PSR

You can edit the macro according to your requirements.