CODEWITHSUNDEEP
excelvba
Lzkatz
Lzkatz

Reputation: 173

Using findnext with two finds

I'm still new to the use of ranges/find/findnext in VBA and was looking for some help. I've successfully implemented the single find -> findnext loop, however I was wondering if it was possible to use findnext with the use of two finds.

The code I'm using it in is the following. I'm using a pizza inventory for simplicity.

The gist is I have a spreadsheet and in column I is the catagories column (Cheese, Meat, Veggie) while Column H (the offset portion) is the type - (Mozzarella, Parmesean, Pepperoni etc etc). What I'm looking to do is compile a list of the different types of one catagory in a certain column without duplicates.

For instance, I want to find all instances of the word "Cheese" in column I. I want the code to first find the word cheese, then give me type (Cheddar Mozzarella etc). The second find is to check whether or not that type already exists in my final column. If it is already present in the column (the second find function = not nothing), then it goes to the next "Cheese". If it is new (the second find function = nothing) then I would like it to put the type in the column and continue on.

As is it is erroring out, and considering the syntax is almost identical to my previous Find/Findnext, with only the addition of an additional FindNext, I realize it might be referencing the wrong one. Is there any way to avoid this?

Note: I realize I could easily just fill in all types in the final column, remove the duplicates, then sort them, but this seems somewhat inelegant and time consuming. I was wondering if there was a more convenient way along the lines that I'm attempting.

    a = 2
    Set c = Range("I:I").Find("Cheese")
    firstadd = c.Address

    If Not c Is Nothing Then

        Do
            temp = c.Offset(0, -1)
            Set d = Range("BA:BA").Find(temp)

                If d Is Nothing Then
                    Cells(a, 53) = temp
                    a = a + 1
                End If

            Set c = Range("I:I").FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstadd
    End If

End Function

Upvotes: 1

Views: 738

Answers (2)

user2140261
user2140261

Reputation: 7993

Try this. 

Dim exists As Integer
a = 2
Set c = Range("I:I").Find("Cheese")
firstadd = c.Address

    Do While Not c Is Nothing And c.Address <> firstadd
        temp = c.Offset(0, -1)
        exists = WorksheetFunction.CountIf(Range("BA:BA"), temp)

            If exists > 0 Then
                Cells(a, 53) = temp
                a = a + 1
            End If

        Set c = Range("I:I").FindNext(c)
    Loop 

End Function

Upvotes: 1

Ray T
Ray T

Reputation: 86

Try a For Each loop to avoid using the find/findnext loop at all.

a = 2
For Each c in Range("I:I") 
    If InStr(c.Value, "Cheese") Then
        temp = c.Offset(0, -1).Value
        If Not Range("BA:BA").Find(temp) Is Nothing Then
            Cells(a, 53).Value = temp
            a = a + 1
        End If
    End If
Next c

Upvotes: 1

Related Questions