Posts by Milan.singh

Hi,

I want to perform an activity (weekly) through VBA which is being done manually.

We create team roster process wise (4 roster) and compiled all the rosters in a master roster

currently I perform this activity either copy paste or through Index match formula.

I need your help to write a VBA code in order to automate this activity

VBA Tool goes to file locations and add those sheets (read-only) in master file and one by one (through loops) copy employee ID and search in masterfie and paster the row value from D to J.
I have attached a sheet for your reference.

Regards,
Milan

Hi,

I have some data and based on that data I want create a graphical dashboard to present to higher management.

1. Team wise
2. When I select any leader, graph only shows agent who reports to that particular leader

I have attached row data for reference.

I am using excel version 2007.

if this process can be automated through VBA that would be great.

Regards,
Milan

Re: Userform to record Login and Logout records

Hi Roy,

Thanks for the prompt assistance. it solves almost 70% of my problems however 30% still left.
I would also like to capture breaks and meeting in this sheet so than I can calculate actual occupancy level of the employee if any (see my task column).

[TABLE="class: cms_table, width: 880"]

[tr]

[td]

LogIn

[/td]

[td]

LogOut

[/td]

[td]

office hours

[/td]

[td]

Task

[/td]

[td]

Start time

[/td]

[td]

End time

[/td]

[td]

Time taken

[/td]

[td]

Comments

[/td]

[/tr]

[tr]

[td]

7/1/15 7:24 AM

[/td]

[td]

7/1/15 4:21 PM

[/td]

[td]

8:57:00

[/td]

[td]

Production

[/td]

[td]

7:24

[/td]

[td]

9:24

[/td]

[td]

2:00:00

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

9:24

[/td]

[td]

13:32

[/td]

[td]

4:08:20

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

13:32

[/td]

[td]

14:01

[/td]

[td]

0:28:40

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

14:01

[/td]

[td]

16:21

[/td]

[td]

2:20:00

[/td]

[td][/td]

[/tr]

[tr]

[td]

7/2/15 7:50 AM

[/td]

[td]

7/2/15 5:03 PM

[/td]

[td]

9:13:35

[/td]

[td]

Production

[/td]

[td]

7:50

[/td]

[td]

9:40

[/td]

[td]

1:50:01

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

9:40

[/td]

[td]

10:07

[/td]

[td]

0:27:47

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

10:07

[/td]

[td]

13:38

[/td]

[td]

3:30:12

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

13:38

[/td]

[td]

14:16

[/td]

[td]

0:37:59

[/td]

[td][/td]

[/tr]

[tr]

[td]

Regards,
Milan

[/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

14:16

[/td]

[td]

17:02

[/td]

[td]

2:46:03

[/td]

[td][/td]

[/tr]

[/TABLE]

Hi,

Can anyone help me to make a userform based login and logout excel sheet, where I can post all daily office activity through userform.
Comments columns should be a free format text box.

e.g
headers:

[TABLE="width: 880"]

[tr]

[td]

LogIn

[/td]

[td]

LogOut

[/td]

[td]

office hours

[/td]

[td]

Task

[/td]

[td]

Start time

[/td]

[td]

End time

[/td]

[td]

Time taken

[/td]

[td]

Comments

[/td]

[/tr]

[tr]

[td]

7/1/15 7:24 AM

[/td]

[td]

7/1/15 4:21 PM

[/td]

[td]

8:57:00

[/td]

[td]

Production

[/td]

[td]

7:24

[/td]

[td]

9:24

[/td]

[td]

2:00:00

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

9:24

[/td]

[td]

13:32

[/td]

[td]

4:08:20

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

13:32

[/td]

[td]

14:01

[/td]

[td]

0:28:40

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

14:01

[/td]

[td]

16:21

[/td]

[td]

2:20:00

[/td]

[td][/td]

[/tr]

[tr]

[td]

7/2/15 7:50 AM

[/td]

[td]

7/2/15 5:03 PM

[/td]

[td]

9:13:35

[/td]

[td]

Production

[/td]

[td]

7:50

[/td]

[td]

9:40

[/td]

[td]

1:50:01

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

9:40

[/td]

[td]

10:07

[/td]

[td]

0:27:47

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

10:07

[/td]

[td]

13:38

[/td]

[td]

3:30:12

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Break

[/td]

[td]

13:38

[/td]

[td]

14:16

[/td]

[td]

0:37:59

[/td]

[td][/td]

[/tr]

[tr]

[td]

Regards,
Milan

[/td]

[td][/td]

[td]

0:00:00

[/td]

[td]

Production

[/td]

[td]

14:16

[/td]

[td]

17:02

[/td]

[td]

2:46:03

[/td]

[td]

[/td]

[/tr]

[/TABLE]

Re: Count blank cells between data

Thanks Batman,

It works perfectly fine.

Hi,

I need a formula or VBA code to count the blank cells (Column A) between non blank cells and put that no on adjacent Column (C).
Example:
[TABLE="width: 205"]

[tr]

[td]

A
PNR

[/td]

[td]

B
Ticket no

[/td]

[td]

C No ofTKT

[/td]

[/tr]

[tr]

[td]

OIVUQI

[/td]

[TD="align: right"]1234567890[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

KVTRCL

[/td]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567892[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567893[/TD]

[td][/td]

[/tr]

[tr]

[td]

MNBOAU

[/td]

[TD="align: right"]1234567894[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

BDCXCN

[/td]

[TD="align: right"]1234567895[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

MHZRLE

[/td]

[TD="align: right"]1234567896[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567897[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567898[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567899[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567900[/TD]

[td][/td]

[/tr]

[tr]

[td]

NJZBNG

[/td]

[TD="align: right"]1234567901[/TD]
[TD="align: right"]2[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567902[/TD]

[td][/td]

[/tr]

[tr]

[td]

PQFKFB

[/td]

[TD="align: right"]1234567903[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

NPQQMN

[/td]

[TD="align: right"]1234567904[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567905[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567906[/TD]

[td][/td]

[/tr]

[tr]

[td]

ICMPRC

[/td]

[TD="align: right"]1234567907[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

FXUVPD

[/td]

[TD="align: right"]1234567908[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567909[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567910[/TD]

[td][/td]

[/tr]

[tr]

[td]

PRDACJ

[/td]

[TD="align: right"]1234567911[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

EUIBCG

[/td]

[TD="align: right"]1234567912[/TD]
[TD="align: right"]1[/TD]

[/tr]

[tr]

[td]

HLCDFR

[/td]

[TD="align: right"]1234567913[/TD]
[TD="align: right"]4[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567914[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567915[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1234567916[/TD]

[td][/td]

[/tr]

[tr]

[td]

IMPIDX

[/td]

[TD="align: right"]1234567917[/TD]
[TD="align: right"]1[/TD]

[/tr]

[/TABLE]