Macro to search dynamic tables

[nikhil83]

My intention is to capture Number of courses attended by an employee.List of courses are provided in Table called 'Index'.

Below mentioned formulea is not useful for me as Table names - MVC,php,Awasc,ruby - and 'Empl Name' needs to be picked dynamically.

=COUNTIFS(MVC!G:G,"Y",E:E,[@[Empl Name]])+
COUNTIFS(php!G:G,"Y",E:E,[@[Empl Name]])+
COUNTIFS(Awasc!G:G,"Y",E:E,[@[Empl Name]])+
COUNTIFS('ruby'!G:G,"Y",E:E,[@[Empl Name]])


I have attached the file . There are 2 tables in First tab named employee . I am working on table named 'Table 7' . I have commented my code as far as i can.Please feel free to make any change to the code as you please . But if you can stick to the logic i followed that would be great . Since i am in a learning mode , if you can make modifications in the code i wrote,it will help me improve.

Code is called on click event of button 'get emp data' in 'Employee' sheet

Following is my logic

1.Pick first employee from the list (Table7).
2.Pick first course from the list .(Table named 'Index')
3.check if employee name figures in course nominee list (It is a table with same name of the course which will be created each time an entry is made in 'Index' .I haven't included that macro in this sheet)
4.if name figures,add 1 else 0
5.Select next course and do 3,4 for next course.Continue this loop till all courses are checked
6.Write total courses attended against employee name in employee list(In Table7)
7.select next employee and follow the sequence from step 2 for next employee.continue this loop till all employees in the list is checked.

 

[Nebu]

Hi:

I have written a macro in module 2
Notes:

I am not using the table 7 or the table in index tab

I am basically using the table Employee and looping through the various training sheets and filling in the details directly to the employee table . You will have to clear employees table but name column and run the macro .

Thanks

 

[nikhil83]

Thanks a lot for pitching in .

macro is working fine for first sheet only . then its giving error "object variable or with block variable not set"

I need to get the total learning hours consumed by each individual,which is mentioned in 'Index' sheet alone . Each course has certain hours associated with it.if a person attend 5 courses,Learning hours of all these 5 courses needs to be added up and placed in learning hours coloumn against employee name.So how do we capture this ?

Also i didn't understand what following statement is for

Sheet6.Range("G" & j) = tbl.DataBodyRange(k, 3)

 

[Nebu]

Hi:

Please find the attached.
Sheet6.Range("G" & j) = tbl.DataBodyRange(k, 3)
is assigning the value of the table to the range G and it is dynamic
Note: Clear the employee table except the name before running the macro.
Thanks

 

[nikhil83]

I think you added up evaluation scores instead of Learning hours . Learning hours for each course is mentioned in 'Index'sheet under coloumn 'Learning' . Eg : total learning hours of tracy is 7 and not 10 as mentioned in the sheet.

 

[Nebu]

Hi:

I have fixed it.

Thanks