Zoeken op (deel van) achternaam en presenteren op apart tabblad

[Tont]

Graag zou ik de VBA code willen hebben die het voor mij mogelijk maakt om in bijgaand Excel bestand over de vier tabbladen heen te kunnen zoeken op 'Achternaam'.
En omdat het originele bestand erg lastige namen bevat die vaak erg op elkaar lijken zou ik graag willen kunnen zoeken op 'Achternaam' door slechts een paar letters in te typen.
Het kamernummer, de achternaam en de voornaam zou ik dan graag afgedrukt zien op het tabblad 'Resultaat' in de cellen A1:C1
Mochten er op basis van de ingegeven karakters meerdere Achternamen gevonden worden die voldoen aan de zoekcriteria dan zie ik die graag op het tabblad 'Resultaat' in de cellen daaronder, dus in A2:C2, A3:C3 enz.

 

[SjonR]

zoiets?

Sub SjonR()

Blad5.Cells(2, 1).Resize(1000, 3).ClearContents

zoekwaarde = InputBox("Vul zoekwaarde in!")

For Each sh In Sheets
    If sh.Name <> "Resultaat" Then
        For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
            If InStr(LCase(sh.Cells(i, 2).Value), zoekwaarde) Then
                Blad5.Cells(Blad5.Range("A" & Rows.Count).End(xlUp).Row + 1, 1).Resize(, 3) = sh.Cells(i, 1).Resize(, 3).Value
            End If
        Next
    End If
Next
End Sub

 

[AHulpje]

Of zo:

 

[Tont]

Dankjewel SjonR en AHulpje. Sorry voor mijn late reactie. Was er nog niet van gekomen.
Beide opties werken prima. Ik ga mijn vraag als opgelost markeren.

 

[Tont]

Nou is het inzicht toch nog weer iets gewijzigd.
Men is van mening dat het toch beter is om het gevonden resultaat niet te presenteren op een apart tabblad "Resultaat" maar de gevonden achternaam of achternamen samen met het kamernummer en de voornaam geel te arceren op het tabblad/de tabbladen waarop ze gevonden worden, dus op het tabblad "Bewoners" en/of "Vertrokken Bewoners"
Hoe moet de in het voorbeeld bijgesloten macro van AHulpje worden aangepast dat er wel kan blijven worden gezocht op de achternaam of een gedeelte van de achternaam maar dat de cellen van de gevonden regel(s) geel worden opgevuld?

 

[snb]

Heb je de les 'autofilter' van je Excelcursus gemist ?

 

[Warme bakkertje]

Sub wb()

zoekwaarde = InputBox("Vul zoekwaarde in!")

For Each sh In Sheets
    sh.UsedRange.Interior.Color = xlNone
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        If LCase(sh.Cells(i, 2).Value) Like "*" & zoekwaarde & "*" Then
            sh.Cells(i, 1).Resize(, 3).Interior.Color = RGB(255, 255, 0)
        End If
    Next
Next

End Sub

 

[JEC.]

Hier ook nog eentje

Sub jec()
 Dim sh, st, a
 st = InputBox("Zoek woord", "Zoeken")
 a = "=isnumber(search(""" & st & """;$B1))"
 For Each sh In Sheets
    With sh.UsedRange.FormatConditions
      If .Count = 0 Then
        .Add(2, , a).Interior.Color = vbYellow
      Else
        .Item(1).Modify 2, , a
      End If
    End With
 Next
End Sub

 

[snb]

Sub M_snb()
   Cells(1).CurrentRegion.AutoFilter 2, "*" & InputBox("(deel van) naam") & "*"
End Sub

 

[JEC.]

Zie #5

 

[Tont]

Warme bakkertje, De macro werkt in het kleine voorbeeld bestand dat ik heb meegestuurd prima. Er wordt gezocht naar een naam of een deel van de naam over de beide tabbladen.
Mijn eigenlijke bestand bevat echter veel meer bewonersnamen. Kan het zijn dat er na een x aantal zoekopdrachten binnen de For Next de macro wordt afgebroken? In mijn eigenlijke bestand wordt alleen op het eerste tabblad "Bewoners" gezocht. Dat zijn 260 rijen. Op het tabblad "Vertrokken Bewoners" wordt nooit iemand gevonden (zijn 55 rijen). Ik krijg echter geen foutmelding.

 

[Warme bakkertje]

Als je voorbeeldbestand representatief is kan er weinig mislopen.

Heb de code geprobeerd in een bestand met 3K rijen op beide tabbladen. Na een 10-tal pogingen nog steeds geen probleem.

Probeer deze anders eens.

Sub wb()

zoekwaarde = InputBox("Vul zoekwaarde in!")

For Each sh In Sheets
    sh.UsedRange.Interior.Color = xlNone
    For i = 2 To sh.Range("B" & Rows.Count).End(xlUp).Row
        If LCase(sh.Cells(i, 2).Value) Like "*" & zoekwaarde & "*" Then
            sh.Cells(i, 1).Resize(, 3).Interior.Color = RGB(255, 255, 0)
        End If
    Next
Next

End Sub

 

[Tont]

Ik denk dat ik weet wat het probleem is Warme bakkertje.
In mijn eigenlijke bestand staan zowel op het tabblad "Bewoners" als op het tabblad "Vertrokken Bewoners" veel meer bewonersnamen.
Om aan te geven welke bewoners bij elkaar horen, bij een speciaal kamernummer, heb ik in mijn bestand gebruik gemaakt van Voorwaardelijke Opmaak!
De rijen van de bewoners die verblijven op een kamer met een even nummer worden met een lichte kleur opgevuld, de rijen van de bewoners op oneven kamernummers hebben geen opmaak.
Het zoeken via de macro op een deel van de naam zorgt er nu voor dat niet alle gevonden rijen geel worden opgevuld want sommigen zijn al opgevuld vanwege die Voorwaardelijke Opmaak.
Is het met VBA ook mogelijk, net zoals bij het beheren van de regels van voorwaardelijke opmaak, dat er is aan te geven dat de opvulling van rijen vanuit de macro op de eerste plaats komt, dus voorrang krijgt boven die van de voorwaardelijke opmaak?

 

[JEC.]

andere oplossing ook bekeken? Werkt met VO via vba. Je kunt dan ook prioriteren via vba

 

[Bram65]

Als je gaat zoeken in de string uit een cel en je gebruikt dan Lcase, dan moet je ook Lcase(Zoekwaarde) gebruiken
Als in de Zoekwaarde een hoofdletter staat zal het item niet worden gevonden

 

[Tont]

De situatie is nu de volgende. De in het voorbeeld aanwezige macro werkt prima. Alleen ik krijg op het tabblad "Bewoners" de gezochte na(a)m(en) niet te zien voorzien van de gele opvulling. Dat gebeurt wel op het tabblad "Vertrokken Bewoners"
Omdat het eigenlijke bestand aanzienlijk langer is en ik graag duidelijk heb welke bewoners er bij elkaar op een kamer verblijven heb ik op het tabblad "Bewoners" gebruik gemaakt van Voorwaardelijke Opmaak (aangemaakt vanuit Excel!)
Daardoor komt bij de zoekopdracht vanuit de macro de gele opvulling niet meer in beeld in een gevonden rij. De rij bevat namelijk al een opvulling.
Is het mogelijk om de aanwezige macro zo aan te passen dat er geen gele opvulling meer over de gevonden cellen komt maar dat de gevonden achternaam samen met het kamernummer en de voornaam worden weergegeven in een andere kleur lettertype, bijvoorbeeld "Bold" en "Red".
Dan kan namelijk de aanwezige voorwaardelijke opmaak vanuit Excel blijven bestaan en wordt toch duidelijk op welke kamer(s) de gezochte bewoners verblijven.

 

[Tont]

Als iemand toch weet hoe de nu aanwezige macro zou moeten worden aangepast zodat de gele opvulling over de al aanwezige opvulling vanuit excel gezet kan worden zou ik helemaal tevreden zijn want die is toch eigenlijk wel het meest duidelijk.
Ik heb gekeken naar de .SetFirstPriority maar krijg het niet voor elkaar. De voorwaardelijke opmaak vanuit Excel krijgt telkens prioriteit boven de voorwaardelijke opmaak vanuit VBA. Ik zou het graag andersom zien.

 

[Warme bakkertje]

Sub wb()

zoekwaarde = InputBox("Vul zoekwaarde in!")

For Each sh In Sheets
    With sh
        With .UsedRange.Font
            .Color = vbBlack
            .Bold = False
        End With
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
            If LCase(.Cells(i, 2).Value) Like "*" & zoekwaarde & "*" Then
                With .Cells(i, 1).Resize(, 3).Font
                    .Color = vbRed
                    .Bold = True
                End With
            End If
        Next
    End With
Next

End Sub

 

[Tont]

Dankjewel Warme bakkertje. Ik denk dat dit de enige manier is om mijn probleem opgelost te krijgen.
Het werkt prima.
Is er nog een mogelijkheid iets aan de macro toe te voegen waardoor er in Excel direct gesprongen wordt naar de eerste naam die aan de zoekwaarde voldoet?