Macro VBA to organize records in employee point report

Asked

Viewed 194 times

0

I need to organize the data that’s in chronological order for a report that’s in date. Macro must first iterate through the CRM column, then through the DATA column and organize the same date records in the same row.

inserir a descrição da imagem aqui

I tried the following Macro, but the condition If Str(Datarel.Value) = Str(Databd.Value) Then seems not to work. Thanks!

     Sub macro()

    Dim i, j, k

    Set Id = Range("B1")
    Set Datarel = Range("B3")
    Set Idbd = Range("H2")
    Set Databd = Range("I2")
    Set Horabd = Range("J2")


    For i = 1 To 1
        For j = 1 To 31        
            For k = 1 To 12

            Datarel.Select

            If Str(Id.Value) = Str(Idbd.Value) Then
                If Str(Datarel.Value) = Str(Databd.Value) Then
                    If Str(Datarel.Offset(0, 1).Value) = "" Then
                        Datarel.Offset(0, 1).Value = Horabd.Value
                    ElseIf Str(Datarel.Offset(0, 3).Value) = "" Then
                        Datarel.Offset(0, 3).Value = Horabd.Value
                    ElseIf Str(Datarel.Offset(0, 4).Value) = "" Then
                        Datarel.Offset(0, 4).Value = Horabd.Value
                    ElseIf Str(Datarel.Offset(0, 5).Value) = "" Then
                        Datarel.Offset(0, 5).Value = Horabd.Value
                    End If
                End If
            End If            

            Set Idbd = Idbd.Offset(1, 0)
            Set Databd = Databd.Offset(1, 0)
            Set Horabd = Horabd.Offset(1, 0)
            Range("G1") = Range("G1") + 1
            Next k
            Set Datarel = Datarel.Offset(1, 0)
            Set Idbd = Range("H2")
            Set Databd = Range("I2")
            Set Horabd = Range("J2")
        Next j        

        Set Id = Id.Offset(34, 0)
    Next i
End Sub
excel vba
  • 1

    The table input data does not match the output data, please do a [mcve]

  • I edited and tried to make it clearer. Thank you.

  • 1

    Apparently the code has no error. On which line gives the error message? Run using the F8 key to see which line generates the error.

  • Actually there is no error. I found that "If Str(Datarel.Value) = Str(Databd.Value) Then" is not working when the condition is true. Is there a problem with Str that doesn’t work with the Data type? I don’t know how it works in VBA.

  • Instead of Str, I used Cdate and it worked. But Else If is executed even if the previous If is true, as if it were a If.

  • Theoretically, if Elseif is being executed, the condition in the previous Elseif has not been met. Run the code line by line with F8 and place the mouse cursor on top of the variables to see their contents. Example, by placing the cursor over Horabd.Value, the VBA shows the value.

  • The "Ifs" If Str(Datarel.Offset(0, 1).Value) = "" Then should have been written without conversion function. Datarel.Offset(0, 1). Value = "" Then It worked out! I would like to thank everyone for their collaboration. Following answer with the result of the code, it may be useful to create a point system in Excel when there is a list of records to be organized in a point report.

Show 2 more comments

1 answer

1

Solved! follows the result.

Sub macro()

    Dim i, j, k

    Set Id = Range("B1")
    Set Datarel = Range("B3")
    Set Idbd = Range("H2")
    Set Databd = Range("I2")
    Set Horabd = Range("J2")


    For i = 1 To 1
        For j = 1 To 31
            For k = 1 To 12

            Datarel.Select

            If Str(Id.Value) = Str(Idbd.Value) Then
                If CDate(Datarel.Value) = CDate(Databd.Value) Then
                    If Datarel.Offset(0, 1).Value = "" Then
                        Datarel.Offset(0, 1).Value = Horabd.Value
                    ElseIf Datarel.Offset(0, 2).Value = "" Then
                        Datarel.Offset(0, 2).Value = Horabd.Value
                    ElseIf Datarel.Offset(0, 3).Value = "" Then
                        Datarel.Offset(0, 3).Value = Horabd.Value
                    ElseIf Datarel.Offset(0, 4).Value = "" Then
                        Datarel.Offset(0, 4).Value = Horabd.Value
                    End If
                End If
            End If

            Set Idbd = Idbd.Offset(1, 0)
            Set Databd = Databd.Offset(1, 0)
            Set Horabd = Horabd.Offset(1, 0)             
            Next k
            Set Datarel = Datarel.Offset(1, 0)
            Set Idbd = Range("H2")
            Set Databd = Range("I2")
            Set Horabd = Range("J2")
        Next j

        Set Id = Id.Offset(34, 0)
        Set Datarel = Id.Offset(2,0)

    Next i
End Sub

Browser other questions tagged excel vba

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