VBA Set a status based on a condition

Hello,

I have a workbook with the worksheets Overview, Table_D and Table_P.


I already have a macro which checks the numbers from Table_D and Table_P in Overview. If a number is not present, it is inserted into Overview with the associated data.

The numbers from Overview have now to be checked cell by cell whether they appear in "Table_D" or "Table_P". If the category "D" with a number is not found in "Table_D", "closed" should be written for this row in column L, the same for P: If the category "P" with the number is not found in "Table_P", "closed" should be written for this row in column L.
If a number from Overview is found in Table_D or Table_P, column L should remain empty. This function is to be integrated into the existing macro.

I would be very grateful if someone can help me

Option Explicit





Sub Daten_in_Overview()

Dim alleD As Variant, alleP As Variant, alleOverview As Variant

Dim lastCell As Long, n As Long, x As Long

Dim available As Boolean



With Worksheets("Table_D")

    alleD = .Range("A3:J" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value

End With



With Worksheets("Table_P")

    alleP = .Range("A3:J" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value

End With





With Worksheets("Overview")

   

    leereZeile = .Cells(Rows.Count, "A").End(xlUp).Row + 1

   

    alleOverview = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value



    For n = 1 To UBound(alleD, 1) - 1

        For x = 1 To UBound(alleOverview)

            If alleD(n, 1) = alleOverview(x, 1) Then   

                available = True

                Exit For

            End If

        Next x

       

        If vorhanden = False Then  

            .Range("A" & lastCell).Value = "D"

            .Range("L" & lastCell).Value = "new" 

           

            For x = 1 To UBound(alleD, 2)

                .Cells(lastCell, x + 1) = alleD(n, x)

            Next x

           

          

           

            alleOverview = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value

            leereZeile = lastCell + 1

        End If

       

        available = False

    Next n

   

    For n = 1 To UBound(alleP, 1) - 1

        For x = 1 To UBound(alleOverview)

            If alleP(n, 1) = alleOverview(x, 1) Then    

                available = True

                Exit For

            End If

        Next x

       

        If available = False Then  

            .Range("A" & lastCell).Value = "P"

            .Range("L" & lastCell).Value = "new" 

           

            For x = 1 To UBound(alleD, 2)

                .Cells(lastCell, x + 1) = alleP(n, x)

            Next x

           

            alleOverview = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value

            leereZeile = lastCell + 1

        End If

       

        available = False

    Next n



End With

End Sub