VBA macro for Exel - copy data from one sheet to another with one condition

Question

VBA macro for Exel - copy data from one sheet to another with one condition

Navigation

#1 by (0 votes)

0

Live personal, I am completely new to VBA and programming. I need help to build a macro for Excel.

Context: In the Excel workbook that is in the following link, I want to develop a VBA macro that fills in the "transatas", "finished" and "wait" fields of the "Report" sheet, with the information contained in the " in finished "and" in a wait "of the sheet" Dia18Set ".

Problem: The "Dia18Set" information is periodically produced completely randomly. Each time the sheet is produced, the "process codes" are not always the same, nor do they appear in the same order.

So, in the macro I have to first check if there is a "Process Code" that I want to fill in the "Report" sheet.

If the "process code" of the "Report" sheet does not exist on the "Dia18Set" sheet, it does not fill in anything.

If it exists, the routine will have to search for the values "in a transact", "in a finished" and "in a wait" in the sheet "Dia18Set" and automatically fill the respective fields of the "Report" sheet.

Attached, follows the Excel file and the file with the code that I developed, but it is not working.

Thanks for the help.

Excel file link - > link

Code developed

Sub transferirDados()
Dim tarefasRecebidas As Long
Dim tarefasProcessadas As Long
Dim tarefasPendentes As Long

Dim IdProcesso As String

Dim ultimaLinhaDia18Set As Long
Dim ultimalinhaRelatorio As Long

Dim contLinhasDia18Set As Long
Dim contLinhasRelatorio As Long

 'Definir numero total de linhas da folha "Relatorio"
ultimalinhaRelatorio = Sheets("Relatorio").Range("A" & Rows.Count).End(xlUp).Row

'Definir numero total de linhas da folha "Dia18Set"
ultimaLinhaDia18Set = Sheets("Dia18Set").Range("A" & Rows.Count).End(xlUp).Row



' O "primeiro For" itera cada umas das linhas da folha "Relatorio" a partir da linha 3 para obter cada um dos "IdProcesso"
For contLinhasRelatorio = 3 To ultimalinhaRelatorio

    IdProcesso = Sheets("Relatorio").Cells(contLinhasRelatorio, 1)

' O segundo For itera cada uma das linhas da folha "Dia18Set" a partir da linha 2, para obter cada um dos "IdProcesso"
    For contLinhasDia18Set = 2 To ultimaLinhaDia18Set

        ' Para cada linha da folha "Dia18Set", verifica se o "Id do processo" da folha "Dia18Set" é igual ao "Id do processo" que esté em análise no "primeiro For"
        If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) Like IdProcesso Then

        'Caso o "IDprocesso" da folha "Dia18Set" seja igual ao da folha "Relatorio", atribui o valor da respetiva linha e coluna às variáves "tarefasRecebidas","tarefasProcessadas" e "tarefasPendentes"
            tarefasRecebidas = Sheets("Dia18Set").Cells(contLinhasDia18Set, 2)
            tarefasProcessadas = Sheets("Dia18Set").Cells(contLinhasDia18Set, 3)
            tarefasPendentes = Sheets("Dia18Set").Cells(contLinhasDia18Set, 4)

            'Atribui à célula do livro "Relatorio" o numero das respetivas tarefas.
            Sheets("Relatorio").Cells(contLinhasRelatorio, 2) = tarefasRecebidas
            Sheets("Relatorio").Cells(contLinhasRelatorio, 3) = tarefasProcessadas
            Sheets("Relatorio").Cells(contLinhasRelatorio, 4) = tarefasPendentes
        End If
    Next contLinhasDia18Set
Next contLinhasRelatorio
End Sub

excel vba office excel-vba

asked by anonymous 18.09.2017 / 15:06

1 answer

Doubt about JSP page through call Strings counter in ArrayList [duplicate]

score 0 · Accepted Answer

If the IDprocess needs to be exactly the same, you do not use Like , since it is usually used to get partial matches with wildcards. Therefore, If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) Like IdProcesso Then can be changed to:

If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) = IdProcesso Then

Note: This code worked for me with Like or =

Where the Dia18Set Worksheet:

+---+--------+-------+---+----+
|   |   A    |   B   | C | D  |
+---+--------+-------+---+----+
| 2 | cod_3  | 10000 | 1 |  4 |
| 3 | cod_4  |  9000 | 2 |  5 |
| 4 | cod_5  |  8000 | 3 |  6 |
| 5 | cod_1  |  7000 | 4 |  7 |
| 6 | cod_7  |  6000 | 5 |  8 |
| 7 | cod_8  |  5000 | 6 |  9 |
| 8 | cod_9  |  4000 | 7 | 10 |
| 9 | cod_10 |  3000 | 8 | 11 |
+---+--------+-------+---+----+

Produced this result:

+----+-----------------+-----------+-------------+----------+
|    |        A        |     B     |      C      |    D     |
+----+-----------------+-----------+-------------+----------+
|  1 |                 | 18/set    |             |          |
|  2 | Código Processo | transatas | finalizadas | aguardar |
|  3 | cod_1           | 7000      | 4           | 7        |
|  4 | cod_2           |           |             |          |
|  5 | cod_3           | 10000     | 1           | 4        |
|  6 | cod_4           | 9000      | 2           | 5        |
|  7 | cod_5           | 8000      | 3           | 6        |
|  8 | cod_6           |           |             |          |
|  9 | cod_7           | 6000      | 5           | 8        |
| 10 | cod_8           | 5000      | 6           | 9        |
| 11 | cod_9           | 4000      | 7           | 10       |
| 12 | cod_10          | 3000      | 8           | 11       |
+----+-----------------+-----------+-------------+----------+

Next try trying to send examples of your tables by ASCII Table Generator

.