Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
23.04.2024 16:27:34
Excel Starthinweis - Arbeitsmappenleistung überprüfen ...
23.04.2024 16:15:16
Mittels VBA vordefinierte Kommentare in Zellen einfügen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1212to1216
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1212to1216
1212to1216
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formellösung für Suchen mal 3

Formellösung für Suchen mal 3
WalterK
Hallo,
Folgendes versuche ich zu erstellen:
1.) Ich importiere Daten in die Spalten A bis ?, die Spaltenanzahl ist variabel.
2.) Über ein Makro fixiere ich 1 blaue Zelle, 1 grüne Zelle und 3 rote Zellen und zwar immer anschließend an die letzte importierte Spalte.
3.) In die grüne Zelle schreibe ich den Spaltenbuchstaben in der gesucht werden soll.
4.) In die 3 roten Zellen schreibe ich die Wortteile die gesucht werden sollen.
5.) Ebenfalls über ein Makro trage ich in (bei meinem Beispiel) Spalte O ab Zeile 3 eine Formel ein, die den Suchbegriff der ersten (1.) rote Zelle in der Suchspalte sucht und dann FALSCH oder WAHR angibt. Natürlich kann die Formel jedes Mal in einer anderen Spalte stehen, eben immer in der ersten freien Spalte.
Meine derzeitige Formel funktioniert für 1 Suchebegriff, ich suche aber eine Lösung für 1 bis 3 Suchbegriffe.
Zum besseren Verständnis habe ich eine Beispieltabelle hochgeladen. Im anderen Forum konnte man nichts anhängen, darum hat man mich vermutlich auch nicht richtig verstanden. Ist auch nicht leicht zu erklären.
https://www.herber.de/bbs/user/74921.xls
Danke und Servus, Walter

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Formellösung für Suchen mal 3
18.05.2011 21:41:06
Josef

Hallo walter,
so?
Tabelle1

 KLMNOPQR
1    KmannWaltersch
2Ü 11Ü 12Ü 13Ü 14    
3Fischer Klaus   WAHR   
4Hartmann Manfred   WAHR   
5Mustermann Karl   WAHR   
6Beckenbauer Walter   WAHR   
7Müller Siegfried   FALSCH   
8Walter Klas   WAHR   
9Burtscher Oswald   WAHR   
10Amermann Walter   WAHR   
11Bauer Hubert   FALSCH   
12Wetscher Walter   WAHR   

Formeln der Tabelle
ZelleFormel
O3{=MIN(LÄNGE(WECHSELN(K3;$P$1:$R$1;"")))<>LÄNGE(K3)}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

« Gruß Sepp »

Anzeige
AW: Formellösung für Suchen mal 3
18.05.2011 22:02:46
WalterK
Hallo Sepp,
besten Dank für die Hilfe.
Deine Formel funktioniert natürlich; bei diesem Beispiel.
Das Problem ist, dass ich die Formeln über ein Makro eintragen lasse, aber erst nach dem Import der Daten feststeht, in welche Spalte die Formeln kopiert werden. Darum hatte ich meine Formel so neutral formuliert, dass sie in jeder Spalte ein Ergebnis liefert, aber eben nur für 1 Suchebegriff.
Es sollten also in der Formel keine Fixpunkte wie K3 oder P1:R1 vorkommen, weil die Formel in einer anderen Spalte nicht mehr funktionieren würde.
Vielleicht bin ich aber total auf dem Holzweg und muss die ganze Sache neu überdenken.
Danke und Servus, Walter
Anzeige
AW: Formellösung für Suchen mal 3
18.05.2011 22:17:25
Josef

Hallo Walter,
und was hindert dich daran, die Zellbezüge durch das Makro entsprechend setzen zu lassen?

« Gruß Sepp »

AW: Formellösung für Suchen mal 3
18.05.2011 22:45:16
WalterK
Hallo Sepp,
Das wäre wahrscheinlich die Idee, mit meinen VBA-Kenntnissen allerdings unmöglich umzusetzen.
Ich lade mal meine Beispieldatei mit meinem bisherigen Code hoch. Den Code habe ich aus Forumanfragen und mit Hilfe anderer Codebeispiele zusammen geschustert, für einen Profi vermutlich unansehbar.
Vielleicht kannst Du mir behilflich sein und diese Zellbezüge entsprechend einbauen. Es sollte im Ergebnis jedenfalls eine Formel in der Spalte sein.
https://www.herber.de/bbs/user/74922.xls
Besten Dank für die Hilfe, Servus Walter
Anzeige
Formellösung für Suchen mal 3 mit VBA
19.05.2011 14:24:37
Erich
Hi Walter,
probier mal Sub Formeleintragen() Dim intI As Integer, lngA As Long, lngC As Long, rngF As Range Dim strF As String, strS(0 To 3) As String, ii As Integer intI = GetCustProp(ActiveWorkbook, "Formelzähler", 4) + 1 If intI > 4 Then intI = 0 SetCustProp ActiveWorkbook, "Formelzähler", intI With ActiveSheet lngC = .Cells(2, .Columns.Count).End(xlToLeft).Column ' letzte Spalte For ii = 0 To 3 strS(ii) = Chr$(64 + lngC + 1 + ii) & "$1" ' Suchzellen Next ii lngA = .Cells(.Rows.Count, Asc(Range(strS(0))) - 64).End(xlUp).Row ' letzte Zeile Set rngF = .Cells(3, lngC + 1).Resize(lngA - 2) rngF.Resize(200).ClearContents ' reinigen .Cells(1, lngC + 2).Resize(, 3).Interior.ColorIndex = xlColorIndexNone Select Case intI Case 0 rngF(1).FormulaArray = _ "=MAX((" & strS(1) & ":" & strS(3) & ">"""")*ISNUMBER(SEARCH(" & _ strS(1) & ":" & strS(3) & "," & .Cells(1, lngC + 1) & "3)))>0" rngF(1).Copy rngF.Offset(1).Resize(lngA - 3) .Cells(1, lngC + 2).Resize(, 3).Interior.ColorIndex = 7 'rosa Case 1 To 3 rngF.FormulaLocal = "=(" & strS(intI) & ">"""")*ISTZAHL(SUCHEN(" & _ strS(intI) & ";" & .Cells(1, lngC + 1) & "3))>0" .Cells(1, lngC + 1 + intI).Interior.ColorIndex = 7 'rosa Case 4 rngF = "Keine Formel" End Select .Cells(1, lngC + 5).FormulaArray = "=""Treffer: ""&COUNTIF(" & _ Left(strS(0), 1) & "3:" & Left(strS(0), 1) & lngA & ",TRUE)" .Cells(1, lngC + 2).Select End With End Sub In dieser Mappe funzt das: https://www.herber.de/bbs/user/74939.xls
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Formellösung für Suchen mal 3 mit VBA
19.05.2011 19:05:19
WalterK
Hallo Erich,
Du hast Recht, in deiner beigefügten Mappe funktioniert der Code.
Ich habe den Code jetzt in ein Modul der Personl.xls kopiert und bei einer Beipieltabelle gestartet.
Es wird bei der Zeile "lngA = .Cells(.Rows.Count, Asc(Range(strS(0))) - 64).End(xlUp).Row" der folgende Fehler angezeigt: Laufzeitfehler 5: Ungültiger Prozeduraufruf oder ungültiges Argument
Keine Ahnung was da falsch sein soll.
Danke und Servus, Walter
Fehlersuche
19.05.2011 19:15:36
Erich
Hi Walter,
füge direkt über der falschen Zeiler bitte mal drei Zeilen ein:
MsgBox lngC
MsgBox strS(0)
MsgBox Range(strS(0))
Was kommt da raus?
In Zeile 1 sollte inSpalte lngC+1 ein Spaltenbuschtabe stehen (in der Beispielmappe steht K in Q1)-
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Fehlersuche
19.05.2011 19:38:06
WalterK
Hallo Erich,
Bei der MsgBox Range(strS(0)) wurde nichts angezeigt, weil ich den Spaltenbuchstaben nicht eingegeben hatte. Kann man es so einrichten, das der Code durchläuft, auch wenn der Spaltenbuchstabe noch fehlt?
Wenn der Code weiterläuft, bleibt er weiter unten bei der Zeile "Range(Cells(2, Plus1), Cells(2, 256)).Select" hängen. Bisher hat diese Zeile keine Probleme gemacht.
Danke und Servus, Walter
offen vergessen
19.05.2011 19:39:06
WalterK
.
AW: Fehlersuche
19.05.2011 20:06:17
Erich
Hallo Walter,
"Kann man es so einrichten, das der Code durchläuft, auch wenn der Spaltenbuchstabe noch fehlt?"
Wie soll das denn gehen? Welchen Sinn soll das haben?
Wie soll der Code Formeln schreiben, ohne den Spaltenbuchstaben zu kennen?
Zum "Plus1":
In meinem Code kommen diese Variablen überhaupt nicht mehr vor.
Plus1 ist etwa lngC+1 oder lngC+2. Vielleicht schaffst du es damit. Wenn nicht, schreib das bitte.
Wenn ich wieder etwas Zeit habe, kann ich deinen restlichen Code viellcith auch umstellen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Fehlersuche
19.05.2011 20:22:41
WalterK
Hallo Erich,
OK, das mit dem Spaltenbuchstaben verstehe ich.
Könnte man dann eine MSG-Box mit dem Hinweis: "Spaltenbuchstaben in ? eingeben und Makro neu starten" für 3 Sekunden zeigen und den Code abbrechen. Statt ? sollte eben die betreffende Zelle angeführt werden.
Zum "Plus1":
Das ist erledigt, habe ich selbst hinbekommen.
Ansonsten bin ich sehr zufrieden mit dem Ergebnis. Die obige Sache wäre noch eine optimale Ergänzung.
Danke auf jeden Fall für Deine Hilfe, Servus Walter
mit Prüfungen
19.05.2011 21:42:00
Erich
Hallo Walter,
hier sind jetzt noch zwei Prüfungen eingebaut: 

Sub Formeleintragen()
Dim intI As Integer, lngA As Long, lngC As Long, rngF As Range
Dim strF As String, strS(0 To 3) As String, ii As Integer
intI = GetCustProp(ActiveWorkbook, "Formelzähler", 4) + 1
If intI > 4 Then intI = 0
SetCustProp ActiveWorkbook, "Formelzähler", intI
With ActiveSheet
lngC = .Cells(2, .Columns.Count).End(xlToLeft).Column             ' letzte Spalte
For ii = 0 To 3
strS(ii) = Chr$(64 + lngC + 1 + ii) & "$1"                     ' Suchzellen
Next ii
If IsEmpty(.Cells(1, lngC + 1)) Then               ' ### neue Prüfung
strF = "Bitte Spaltenbuchstaben in Zelle "
ElseIf Len(.Cells(1, lngC + 1)) = 1 Then
Select Case .Cells(1, lngC + 1)
Case "A" To "Z"
Case "a" To "z"
Case Else
strF = "Bitte richtigen Spaltenbuchstaben in Zelle "
End Select
ElseIf Len(.Cells(1, lngC + 1)) = 2 Then
Select Case .Cells(1, lngC + 1)
Case "AA" To "IV"
Case "aa" To "iv"
Case Else
strF = "Bitte richtige Spaltenbuchstaben in Zelle "
End Select
Else
strF = "Bitte Spaltenbuchstabe in Zelle "
End If
If strF > "" Then
MsgBox strF & Replace(strS(0), "$", "") & " eingeben.", _
vbExclamation, "Formeln eintragen"
Exit Sub
End If
lngA = .Cells(.Rows.Count, _
Range(Range(strS(0)) & "1").Column).End(xlUp).Row ' letzte Zeile
If lngA """")*ISNUMBER(SEARCH(" & _
strS(1) & ":" & strS(3) & "," & .Cells(1, lngC + 1) & "3)))>0"
rngF(1).Copy rngF.Offset(1).Resize(lngA - 3)
.Cells(1, lngC + 2).Resize(, 3).Interior.ColorIndex = 7  'rosa
Case 1 To 3
rngF.FormulaLocal = "=(" & strS(intI) & ">"""")*ISTZAHL(SUCHEN(" & _
strS(intI) & ";" & .Cells(1, lngC + 1) & "3))>0"
.Cells(1, lngC + 1 + intI).Interior.ColorIndex = 7       'rosa
Case 4
rngF = "Keine Formel"
End Select
.Cells(1, lngC + 5).FormulaArray = "=""Treffer: ""&COUNTIF(" & _
Left(strS(0), 1) & "3:" & Left(strS(0), 1) & lngA & ",TRUE)"
.Cells(1, lngC + 2).Select
End With
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Perfekt, läuft wie geschmiert! Besten ...
19.05.2011 22:01:40
WalterK
Hallo Erich,
... Dank für Deine Mühe und Geduld.
Servus, Walter

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige