HausUkrop
HausUkrop

Reputation: 61

Cannot FIND anything

I cannot find entries from the same column inside itself. Why? What is wrong with my code? I get the error that types are not compatible because it finds Nothing for the first entry.

Dim Namensspalte As Range, firstEntry As Range, secondEntry As Range
Set Namensspalte = iMAw.Range("E2:E1700")
With Namensspalte
For Each c In Namensspalte
    Set firstEntry = .Find(c, xlValues, xlWhole)
    If Not firstEntry Is Nothing Then
        firstEntry.Offset(0, 3).Value = firstEntry.Offset(0, -4).Value
    Else
        MsgBox "Error"
    End If
    Set secondEntry = .FindNext(c)
    If Not secondEntry Is Nothing Then
        secondEntry.Offset(0, 4).Value = secondEntry.Offset(0, -4).Value
    End If
Next
End With

Upvotes: 1

Views: 51

Answers (1)

R3uK
R3uK

Reputation: 14537

This should work properly :

Sub test_HausUkrop()

Dim Namensspalte As Range, firstEntry As Range, secondEntry As Range, _
    c As Range, FirstAddress As String
Set Namensspalte = iMAw.Range("E2:E1700")
With Namensspalte
    For Each c In .Cells
        Set firstEntry = .Find(What:=c.Value2, _
                                After:=.Cells(1, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False, _
                                SearchFormat:=False)
        If Not firstEntry Is Nothing Then
            FirstAddress = firstEntry.Address
            firstEntry.Offset(0, 3).Value = firstEntry.Offset(0, -4).Value

            Set secondEntry = .FindNext(firstEntry)
            'Look until you find again the first result
            If Not secondEntry Is Nothing Then
                If secondEntry.Address <> FirstAddress Then
                    secondEntry.Offset(0, 4).Value = secondEntry.Offset(0, -4).Value
                Else
                End If
            Else
            End If
        Else
            MsgBox "Error"
        End If
    Next c
End With

End Sub

Upvotes: 1

Related Questions