Excel VBA comparing variants in if statement

let me explain my problem: I have two excel sheets "data" and "output". In the first column of each sheet there are numbers that need to be compared. The numbers look like: 3081671 If I find a number in sheet output that matches the current iteration in the sheet data, I want that the value in the same line in sheet data is written right next to the number in sheet output. This actually works. However, some of the numbers in sheet data look like this: V3081671A When iterating and finding such a number in sheet data, I cut the V and A such that only the integer is left.

However, for those cases the if statement "If test = comp Then..." is false, even though test and comp should contain exactly the same strings. Why is this the case?

Any help to solve the problem is really appreciated. If I didn't describe it clear enough, feel free to ask.

Sub MatchNumbers()
    Dim i As Integer
    Dim j As Integer
    Dim buffer As Variant
    Dim comp As Variant
    Dim test As Variant
    Dim inp As Variant


    j = 2
    While IsEmpty(ActiveSheet.Cells(j, 1)) = False
        ActiveSheet.Cells(j, 2) = "NV"
        j = j + 1
    Wend
    
    i = 2
    inp = Tabelle1.Cells(i, 1)
    While IsEmpty(inp) = False
        If Not IsNumeric(Left(inp, 1)) And Len(inp) >= 9 Then
            comp = Mid(inp, Len(inp) - 7, 7)
        Else
            comp = inp
        End If
    
        j = 2
        test = ActiveSheet.Cells(j, 1)
        Do While IsEmpty(test) = False
                If Left(inp, 1) = "V" Then
                    MsgBox ("Neue Zeile!")
                    ActiveSheet.Cells(j, 1).EntireRow.Insert Shift:=xlDown
                    j = j + 1
                    ActiveSheet.Cells(j, 1) = inp
                End If
                ActiveSheet.Cells(j, 2) = Tabelle1.Cells(i, 3)
                Exit Do
            End If
            j = j + 1
            test = ActiveSheet.Cells(j, 1)
        Loop
        i = i + 1
        inp = Tabelle1.Cells(i, 1)
    Wend
End Sub