Thread: find variables with wildcards.

Hello,

Last year i've made a small tool which extracted some data from a large sheet.
Many people used the tool with good effort.

Now someone asked me to change the tool, instead of searching for totals, we now would like to see what everyone is selling. (and a total)

I've tried some things but it won't work. I would like to put some extra search 'n copy function between the existing code. (Between "Producten" & "Totalen Conversie")

The biggest problem is that some search strings are the same, (See "Invoerblad" in example) the only thing that i would like to search is on the first 3 numbers, when they are the same, count as the same.

Here is the code which i use to extract the totals:

Sub FilterAgents()

Application.ScreenUpdating = False
    uitvoerBlad.Activate
    'uitvoerBlad.Unprotect (Constanten.wachtwoord)
    uitvoerBlad.Range("C5:R104").Value = ""

With InvoerBlad.Range("B1", InvoerBlad.Range("B" & Rows.Count).End(xlUp))
    Set c = .Find(What:="Medewerker:", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            n = uitvoerBlad.Range("C" & Rows.Count).End(xlUp).Row + 1
            v = Application.Match(c.Offset(, 1), uitvoerBlad.Columns("W:W"), 0)
            If IsNumeric(v) Then
            
            ' Naam en Code
                c.Offset(0, 1).Copy
                uitvoerBlad.Range("C" & n).PasteSpecial xlPasteValues
                uitvoerBlad.Range("D" & n).Value = Application.Index(uitvoerBlad.Columns("X:X"), v, 1)
                
            ' Producten
                Set p613 = .Find(What:="613", After:=c, Lookat:=xlPart, _
                               SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False, SearchFormat:=False)
                Set tot = InvoerBlad.Cells.Find(What:="Totaal", After:=c, Lookat:=xlWhole, _
                                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                          MatchCase:=False, SearchFormat:=False)

                If Not p613 Is Nothing And Not tot Is Nothing Then
                    InvoerBlad.Cells(c1.Row + 1, c2.Column).Copy
                    uitvoerBlad.Range("E" & n).PasteSpecial xlPasteValuesAndNumberFormats 'Aantal 613

                End If
            
            ' Totalen Conversie
                Set c1 = .Find(What:="Conversie", After:=c, Lookat:=xlWhole, _
                               SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False, SearchFormat:=False)
                Set c2 = InvoerBlad.Cells.Find(What:="Totaal", After:=c, Lookat:=xlWhole, _
                                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                          MatchCase:=False, SearchFormat:=False)

                If Not c1 Is Nothing And Not c2 Is Nothing Then
                    InvoerBlad.Cells(c1.Row + 1, c2.Column).Copy
                    uitvoerBlad.Range("P" & n).PasteSpecial xlPasteValuesAndNumberFormats 'Aantal Sales
                    InvoerBlad.Cells(c1.Row + 2, c2.Column).Copy
                    uitvoerBlad.Range("Q" & n).PasteSpecial xlPasteValuesAndNumberFormats 'Conversie Calls
                    InvoerBlad.Cells(c1.Row, c2.Column).Copy
                    uitvoerBlad.Range("R" & n).PasteSpecial xlPasteValuesAndNumberFormats 'Conversie Sales


                End If
            End If
            Set c = .Find(What:="Medewerker:", After:=c, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        Loop While c.Address <> firstaddress
    End If
End With
' sorteer uitkomst op conversie.

    Range("C5:R104").Select
    Selection.Sort Key1:=Range("R5"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("C5").Select

      'uitvoerBlad.Protect (Constanten.wachtwoord)
Application.ScreenUpdating = True



End Sub

The original file is not included, it's a bit big, If someone likes to see. send me a PM

Thanks in Advance