VBA Hour / employee control

Question

VBA Hour / employee control

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I've been trying to learn the commands in VBA for a few days to automate a company employee's time tracking worksheet.

I have a sheet with employee names and a calendar as follows:

                        horas   Local   Projeto horas   Local   Projeto ....
 COLABORADOR    FUNÇÃO  8-Mar   8-Mar   8-Mar   9-Mar   9-Mar   9-Mar
  felipe       exemplo  9:04    
    joão       exemplo

And another sheet where I load the time bank I pull from the system in the following way.

Shows the day with time of entry and exit.

felipe  8-Mar   7:50
felipe  8-Mar   16:54
joão    8-Mar   7:43
joão    8-Mar   17:00
...

I need to compare the employee's name and allocate hours worked on the day. The location and project will be uploaded by another medium.

macro excel vba

asked by anonymous 10.03.2015 / 02:20

2 answers

How to update a JLabel periodically? how to write a name using pointers

score 1 · Answer 1

Following code that can be used as a basis:

   Sub teste()
        'Linha/Coluna BASE
        Dim linPlan1 As Integer
        Dim colPlan1 As Integer
        colPlan1 = 1
        linPlan1 = 4

        'FUNCIONARIO
        Dim nomeFunc As String

        Do While Plan1.Cells(linPlan1, colPlan1) <> ""

            nomeFunc = Plan1.Cells(linPlan1, colPlan1)
            hrInicio = Plan1.Cells(linPlan1, colPlan1 + 2)
            hrFim = Plan1.Cells(linPlan1 + 1, colPlan1 + 2)

            horasTrabalhadas = DateDiff("n", hrInicio, hrFim) / (24 * 60)
            'Plan1.Cells(4, 4) = horasTrabalhadas

            'PROCURA NOME FUNCIONARIO NA PLANILHA2
            Dim linPlan2 As Integer, colPlan2 As Integer
            colPlan2 = 1
            linPlan2 = 4
            Do While Plan2.Cells(linPlan2, colPlan2) <> ""
                If nomeFunc = Plan2.Cells(linPlan2, colPlan2) Then
                    Plan2.Cells(linPlan2, colPlan2 + 2) = horasTrabalhadas
                    Exit Do
                End If
                linPlan2 = linPlan2 + 1
            Loop

            linPlan1 = linPlan1 + 2 'pula para proximo nome planilha1
        Loop
    End Sub

WORKSHEET AND RESULTS:

NOTE:The"time" fields must be in the custom format: Time

score 1 · Answer 2

This is also possible using dot matrix formulas , see below:

Formula: = SEP (Plan2! $ A $ 2: $ A $ 100 = Plan1! $ A3; SE (Plan2! $ B $ 2: $ B $ 100 = Plan1! C $ 2; Plan2! $ C $ 2: $ C $ 100) MINIMUM (SE (Plan2! $ A $ 2: $ A $ 100 = Plan1! $ A3; SE (Plan2! $ B $ 2: $ B $ 100 = Plan1! C $ 2; Plan2! $ C $ 2: $ C $ 100) p>

After you finish the formula, press Ctrl + Shift + Enter to determine what a matrix formula is.