August 31, 2010

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.
Post a Comment

Featured Post

Java Introdcution

Please send your review and feedback to psrdotcom@gmail.com