Anzeige
Archiv - Navigation
1180to1184
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Suchen mit Makro

Suchen mit Makro
Michael
Guten Morgen,
mich plagt zur Zeit folgendes Problem:
In einem Tabellenblatt habe ich in der Spalte A (ab A2) ca. 300 Kunden-ID's (alphanumerisch) stehen. Diese ID kann in den ca. 50 anderen Tabellenblättern (als Eintrag in einer Zelle) Verwendung finden.
Nun würde ich gerne alle Blattnamen ab C1 als Überschrift hinterlegen und ein Makro sollte nun untersuchen, ob sich eine ID auf dem jeweiligen Blatt befindet oder nicht. Toll wäre es natürlich, wenn 0 = nicht vorhanden, 1 = einmal vorhanden, 2 = zweimal vorhanden usw. vom Makro in der Matrix hinterlegt wird!
Mit dem Makro-Rekorder kriege ich das einfach nicht hin.
In der Spalte B wollte ich mit der Funktion Anzahl2 ermitteln, ob und wie oft eine ID verwendet wird, das erklärt die Lücke zwischen ID-Spalte (A) und Bereich mit Blattnamen (ab C).
Hinweis: die ID sind in Spalte A ohne Lücke hinterlegt, darunter kommt nix mehr; ebenso die Blattnamen in Zeile 1.
Für Eure Unterstützung bedanke ich mich schon mal ganz herzlich
Michael

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
und Zählenwenn() geht nicht ?
30.09.2010 07:18:18
Matthias
Hallo Michael
Warum benutzt Du nicht einfach Zählenwenn()
Userbild
Tabelle1

 BC
2A-Team14
3A-Team21
4A-Team33

Formeln der Tabelle
ZelleFormel
C2=ZÄHLENWENN(Tabelle2!1:65536;B2)
C3=ZÄHLENWENN(Tabelle2!1:65536;B3)
C4=ZÄHLENWENN(Tabelle2!1:65536;B4)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Matthias
Anzeige
AW: und Zählenwenn() geht nicht ?
30.09.2010 07:45:01
Michael
Hallo Matthias
da ich über 300 ID's in Spalte habe und mehr als 50 Blätter ( mit Bereichen von A1 bis DS10000), wird das vielleicht zu langsam. außerdem muss ich in der Formel ja den Blattnamen aus der Zeile 1 abgreifen. Das geht aber mit Zählenwenn und Indirekt einfach nicht.
Vielen Dank und Gruß
Michael
AW: hier mal eine reine VBA-Lösung
30.09.2010 08:22:38
Michael
Hallo Matthias,
na das kommt der Lösung schon sehr nahe!
ich habe mal eine Dummydatei erstellt. Der einzige Unterschied zur realen Datei: Ich habe in Spalte A ca. 300 Einträge und in Zeile 1 ca. 50 Blattnamen (beides ohne Lücken!). (ändert sich laufend)
https://www.herber.de/bbs/user/71707.xlsx
Toll wäre es, wenn das Makro den relevanten Bereich gelb) mit den entsprechenden Werten füllt.
Wenn das gehen würde, das echt super!!
Vielen Dank für Deine Mühe und Gruß
Michael
Anzeige
Usedrange ist zu langsam!
30.09.2010 08:36:26
Matthias
Hallo
Ich glaube mein Bsp. war ungünstig,
Die Schleife darf nicht übers ganze UsedRange laufen, sondern nur über die benutzten Zellen.
also nur über die SpecialCells(xlCellTypeConstants)
Denn wenn irgenwann mal z.B. in der letzte Zelle ein Wert steht (oder auch nur mal stand!) läuft der Code ewig
Aber schau Dir erst mal die anderen Vorschläge an (von Jogy und fcs)
Gruß Matthias
Danke
30.09.2010 09:02:20
Michael
Hallo Matthias,
vielen Dank für Deine Mühe!
Gruß
Michael
AW: und Zählenwenn() geht nicht ?
30.09.2010 08:16:30
JogyB
Hallo Michael,
natürlich geht das mit Indirekt, z.B. wenn die Suchbegriffe in Spalte A stehen und die Tabellennamen in Zeile 1:
=ZÄHLENWENN(INDIREKT("'" & B$1 & "'!1:65536");$A2)
Aber das ist in der Tat extrem langsam, wenn Du wirklich das komplette Blatt prüfen willst.
Gruß, Jogy
Anzeige
AW: und Zählenwenn() geht nicht ?
30.09.2010 09:01:30
Michael
Hallo Jogy,
vielen Dank für die Nachhilfe für die Funktion Indirekt! Kan ich gut gebrauchen.
Gruß
Michael
AW: Suchen mit Makro
30.09.2010 08:20:19
fcs
Hallo Michael,
Makro schaut etwa wie folgt aus.
Als kleine Erleiterung hab iach auch ein Makro zum Eintragen der Tabellennamen eingebaut.
Gruß
Franz Sub CountIDs() Dim wksZiel As Worksheet Dim wks As Worksheet Dim vID As Variant, sTab As String Dim Zeile As Long, Spalte As Long, Spalte_L As Long, AnzahlID As Long Set wksZiel = Worksheets("Übersicht") 'Name ggf. anpassen With wksZiel 'Letzte Spalte mit Tabellen-Name Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column If Spalte_L >= 3 Then .Range(.Columns(3), .Columns(Spalte_L)).ClearContents Call TabellenNamen 'Letzte Spalte mit Tabellen-Name Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Zellen in Spalte A ab Zeile 2 abarbeiten For Zeile = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row vID = .Cells(Zeile, 1).Value For Spalte = 3 To Spalte_L Set wks = Worksheets(.Cells(1, Spalte).Text) With wks AnzahlID = Application.WorksheetFunction.CountIf(.UsedRange, vID) End With .Cells(Zeile, Spalte).Value = AnzahlID Next 'Formel Spalte B .Cells(Zeile, 2).FormulaR1C1 = "=SUM(R[0]C[1]:R[0]C[" & Spalte_L - 2 & "])" Next End With End Sub Sub TabellenNamen() 'Tabellennamen in Übersicht in Zeile 1 ab Spalte C eintragen Dim wksZiel As Worksheet Dim wks As Worksheet Dim Spalte As Long Set wksZiel = Worksheets("Übersicht") 'Name ggf. anpassen Spalte = 2 For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case wksZiel.Name 'do nothing Case Else Spalte = Spalte + 1 wksZiel.Cells(1, Spalte).Value = "'" & wks.Name End Select Next End Sub
Anzeige
perfekt
30.09.2010 09:00:30
Michael
Hallo Franz,
lauft perfekt durch!
Vielen Dank
Michael

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige