Вы просто не хотите дубликатов? Используйте скриптовый словарь.
Dim dict as Object
set dict = CreateObject("Scripting.Dictionary")
Dim i as Long
For i = 2 to 9
dict(cells(i,1)) = 1
Next
Dim dictKey as Variant
i = 1
For Each dictKey in dict.Keys()
cells(i,2) = dictKey
i = i+1
Next
Или в конце:
dim dictVal as string
For i = 2 to 9
dictVal = cells(i,1)
If Dict(dictVal).Exists Then
cells(i,2) = cells(i,1)
Else: cells(i,2) = "N/A"
End if
Next
Или если вы хотите грубый способ сделать это
Sub testing()
Dim i As Long
Dim j As Long
Dim c As Range
For i = 2 To 9
With Range(Cells(i + 1, 1), Cells(9, 1))
Set c = .Find(Cells(i, 1), LookIn:=xlValues)
If c Is Nothing Then
Cells(i, 2) = Cells(i, 1)
Else: Cells(i, 2) = "N/A"
End If
End With
Next
End Sub