VBA Control of hours/employees

Asked

Viewed 1,065 times

1

I’ve been trying for days to learn the commands in VBA to automate a company’s employee hours control spreadsheet.

I have a sheet with the names of the employees 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 charge the bank of hours pulling from the system with the following form.

Shows the day with check-in and check-out times.

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 name and allocate the hours worked on the day. The site and project will be loaded by another means.

excel vba macro

2 answers

1

Follows code that can be used as base:

   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


SPREADSHEET AND RESULT:

inserir a descrição da imagem aqui

OBS: The "time" fields must be in custom format: Time

1

This is also possible using matrix formulae, see below: inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

Formula: =MAXIMUM(SE(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)))

After finishing the formula, press Ctrl+Shift+Enter to determine that it is a matrix formula.

Browser other questions tagged excel vba macro

You are not signed in. Login or sign up in order to post.