Zelladressen der Suchergebnisse auflisten

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 11:06:28

Hallo, alle zusammen !
Ich sitze nun schon seit Stunden dran....:

Rahmenbedingungen:
Arbeitsmappe mit etwa 60 Tabellenblättern,
jedes Tabellenblatt enthält unterschiedlich viele Spalten und Zeilen,
jedes Tabellenblatt enthält in der äussersten rechten Spalte eine Zelle, die den Textstring "Bemerkung" oder "Bemerkungen" enthält.

bereits gelöst:
Makro, das ein neues Tabellenblatt namens "TabListe" erstellt und am Anfang aller Tabellen positioniert,
Makro, das alle Tabellenblatt-Namen in "TabListe" in die Spalte B listet,
Makro, das in allen Tabellenblättern "Blatt schützen" ausführt,
Makro, das in allen Tabellenblättern "Blattschutz aufheben" ausführt.

nicht gelöst / Problem:
ich möchte von allen Tabellenblättern jeweils die Zelladresse der letzten benutzte Zelle,
sowie die Zelladresse der Zelle, die den String "Bemerkung" bzw. "Bemerkungen" enthält,
in "TabList" in der Spalte "C" respektive "D" auflisten, (eben in der Zeile des jeweils zugehörigen Tabellenblattes)

Das Suchen an sich ist nicht das Problem - allerdings schaffe ich es nur, das Suchergebnis jeweils MARKIEREN zu lassen, nicht jedoch die ZELLADRESSE in eine Zelle ausgeben zu lassen.

Wer hat dazu denn eine Idee ?
(Ich geh mal davon aus, daß ich mich einfach nur verrannt habe, und den Wald vor lauter Bäumen nicht mehr sehe ? )

viiiieeelen Dank im voraus !
Goldie

Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 11:46:41

Hi Goldie

hier der Code, wie du die Bemerkungen finden kannst. Das Selektieren ist nur zum Testen nötig.
Die erste und die letzte Zeile eines Tabellenblattes entweder mit usedRange oder mit End(xlUp) herausfinden.



Sub Suchen()
Dim cell, iRow
iRow = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Row
For Each cell In Worksheets(1).Range("E1:E" & iRow)
    Worksheets(cell.Text).Select
    With Worksheets(cell.Text).UsedRange    'usedrange gibt den verwendeten Bereich zurück
        .Columns(.Columns.Count).Select     'wählt die letzte Zeile an
            
    '.find("Bemerkung" entspricht Suchen-Menü-Befehl,
    ' sucht Zelle mit "Bemerkung" und auch "Bemerkungen"
            
        MsgBox .Columns(.Columns.Count).Find("Bemerkung").Row   'gibt Zeile
        MsgBox .Columns(.Columns.Count).Find("Bemerkung").Column 'gibt Spalte
        MsgBox .Columns(.Columns.Count).Find("Bemerkung").Address 'gibt Adresse
'schreiben
        cell.Offset(0, 1).Value = .Columns(.Columns.Count).Find("Bemerkung").Address
    End With
        
Next cell
End Sub



Gruss, Martial


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 12:01:28

Hallo, Martial !

Erstmal "Danke für schnell" !!

beim Ausführen des Makros gibts einen "Laufzeitfehler '9' "Index ausserhalb des gültigen Bereichs",
der Debugger verweist dann auf die Zeile:
"Worksheets(cell.Text).Select"
??
Kann ich nicht deuten ....


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 12:32:53

Gib mal mit msgbox cell.text den Inhalt aus

oder mach erst mal nur:

For Each cell In Worksheets(1).Range("E1:E" & iRow)
cell.select
Next cell


und schau an, was das Makro macht (mit Einzelschritt...)
dann siehst du den Fehler bzw. kannst Stück für Stück erweitern.


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 12:34:51

Aha...

die TabellenNamen sind bei dir natürlich in Spalte B
das musst du anpassen (und die anderen Bezüge evtl. auch):

For Each cell In Worksheets(1).Range("B1:B" & iRow)
...
...


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 12:50:59

Martial ?
Irgendwie komm ich mir so ganz arg ahnungslos vor... ;-)

oder ich hab meinen "Raffnix-Tag"...
also: mal am Beispiel:

[Listing:

Sub Suchen2()
Dim cell, iRow
For Each cell In Worksheets(1).Range("B1:B" & iRow)
cell.Select
iRow = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Row
MsgBox (iRow)
Next cell
End Sub

Listing Ende]

Bei obigem bekomme ich genau 1 mal eine "1" angezeigt,
setzé ich stattdessen
"MsgBox (cell.text)"
ein, gibts den erwähnten "Laufzeitfehler" (auch bei "msgbox cell.text" ohne Klammern)

...

Ich experimentier mal weiter....
(was nicht heisst, daß Du Dich jetzt ausklinken darfst *gg*)
Goldie


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 13:06:15

Hei Goldie

vielleicht erklär ich das auch sch***** ;-)

Also deine Tabellenblattnamen sind in Spalte B. Dann muss es so aussehen:
----------------------------------------------
Sub Suchen()
Dim cell, iRow

iRow = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
'das 2 steht für Spalte B,
'war vorher falsch
'jetzt sollte in iRow die Zeile deines letzten TabellenblattNamens stehen

For Each cell In Worksheets(1).Range("B1:B" & iRow)
cell.Select
MsgBox cell.Text 'ohne Anführungszeichen, da cell.text bereits ein String ist

Next cell
----------------------------------------


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 13:18:17

@ Martial

*lautlach*
Danke Dir... bin ich vor zwei Sekunden auch drauf gekommen !

Aber nun:
"Laufzeitfehler 1004
Die Select-Methode des Range-Objekts konnte nicht ausgeführt werden"
Der Debugger markiert dann die Zeile, in der steht "cell.Select"

[

Sub Suchen2()
Dim cell, iRow
iRow = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each cell In Worksheets(1).Range("B1:B" & iRow)
cell.Select
MsgBox cell.Text
Next cell
End Sub

]

Ich glaub, so langsam sollten wir mal über die Vorteile eines Messengers nachdenken - das Ergebnis kann man ja abschließend ins Forum stellen ;-)

Grüßle
Goldie


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 13:23:28

In dem Fall ist nicht das richtige Tabellenblatt aktiviert. Du kannst das .select rausnehmen, oder vorher mit worksheets(1).select das 1.Tabellenblatt aktivieren...


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 14:09:28

@ Martial
(Danke Dir mal für Deine Geduld !)

Hier mein letzter Ansatz für heute:
[

Sub Suchen2()
Dim cell, iRow
Sheets(1).Select
'iRow = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
iRow = 13                       'nur 5mal (von B8(=1.Eintrag) bis B13, reicht zum Testen)
For Each cell In Worksheets(1).Range("B8:B" & iRow)
    MsgBox cell.Text            'funzt bis hierhin
    Worksheets(cell.Text).Select 'funzt auch :-)
        With Worksheets(cell.Text).UsedRange
            '.Columns(.Columns.Count).Select
            MsgBox .Columns(.Columns.Count).Find("Bemerkung").Row   'soll Zeile ermitteln; aber hier hakts (siehe unten)
            'MsgBox .Columns(.Columns.Count).Find("Bemerkung").Column 'gibt Spalte
            'MsgBox .Columns(.Columns.Count).Find("Bemerkung").Address 'gibt Adresse
            'cell.Offset(0, 1).Value = .Columns(.Columns.Count).Find("Bemerkung").Address
        End With
Next cell
End Sub
]

dort, wo ich mit "siehe unten" kommentiert habe,
erhalte ich über die Hilfefunktion (F1) von Excel folgenden Hinweis:
"Nicht eindeutige Auswahl
Sie haben entweder kein Schlüsselwort ausgewählt, oder Hilfe zu einer Komponente der integrierten Entwicklungsumgebung angefordert. Falls Sie versucht haben, ein Schlüsselwort auszuwählen, versuchen Sie es erneut, indem Sie ein einzelnes Schlüsselwort auswählen"

Nafein - wie muss ich also nun diese Zeile umpfriemeln ?
MsgBox .Columns(.Columns.Count).Find("Bemerkung").Row

Ich mutmaße mal, daß hier das neu erstellte "TabList" - Blatt nach dem Wort "Bemerkung" durchsucht wird - und DORT ist natürlich keines enthalten...
Stellt sich (mir) also die Fräge, wie kann ich an dieser Stelle eine "Fehlermeldung, weil nicht gefunden" unterdrücken...

aber wie gesagt - ist der letzte Versuch,
wenn das auch nicht klappt, dann gebe ich hier erstmal auf und mach nen Lehrgang *gg*
(ich denke, ich nerv so langsam)
Goldie


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 14:48:15

Erklärung, was die Befehlszeile macht:

With Worksheets(cell.Text).UsedRange
.Columns(.Columns.Count).Find("Bemerkung").Row
end with

worksheets(cell.text) - spricht das betreffende Worksheet an
.usedrange - gibt den verwendeten Bereich zurück
.columns(i) - gibt die Spalte i des verwendeten Bereiches zurück
.columns(.columns.count) - gibt die letzte Spalte (dort steht doch dein "Bemerkung" oder?)
.find(...) - sucht in diesem Bereich (also der letzten Spalte) nach "Bemerkung"
- gibt tatsächlich einen Fehler aus, wenn es nichts findet - siehe unten
.row/.address - gibt Zeile bzw. Adresse der Zelle zurück



Versuch das ganze so zu implementieren, dass es keinen Fehler gibt (also nur dort suchen, wo du weisst, dass du was findest - vor allem beim Testen)
wird mit
.Columns(.Columns.Count).Select
der richtige Bereich selektiert (wo "Bemerkung" drinn steht)???


später kannst du einen Fehler mit
on error resume next/on error goto ERRORHANDLER ausschalten
schau dazu in der Hilfe nach - mach es aber noch nicht jetzt!!!! sonst siehst du nicht, wo die Fehler auftreten...

Für die Hilfe (F1): Nur EIN Teil des Befehles auswählen (z.B: find) ohne Punkt oder anderes...

Wenn du beim Makro schreiben F8 drückst, wird ein Einzelschritt ausgeführt... Einzeln durchgehen und überprüfen, was selektiert wird...


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 14:54:27

@ Martial

dollen Dank !
DAS ist hilfreich.....

Nochmal Danke für Deine Geduld ;-)

Goldie


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Martial
Geschrieben am: 21.11.2003 15:01:39

Dafür ist das Forum ja da

Schreib aber unbedingt, ob es auch funktioniert....

Gruss und schönes Wochenende, Martial


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 21.11.2003 15:10:32

mach ich AUF JEDEN FALL, verlaß Dich drauf
(das bin ich Dir ja wohl mindestens schuldig ;-) )
Falls wir uns vorher nicht mehr "über den Weg laufen",
Dir und deinen Lieben auch ein schönes WE !

liebe Grüße
Goldie


Bild


Betrifft: AW: Zelladressen der Suchergebnisse auflisten
von: Goldie
Geschrieben am: 22.11.2003 17:59:57

@ Martial

"Isch 'abe Fertick" !!

hier, - wie versprochen, den (relevanten) Teil
des Scripts; das ganze hat mittlerweile eien Umfang von 437 Zeilen....
(Mail ich Dir aber gern zu, wenn Du magst - meine Adresse steht ja "da irgendwo")

Dir jedenfalls nochmal vielen Dank - und ein schönes WE !

liebe Grüße
Goldie

'Zurück ins erste Blatt und aus Spalte B die Namen
'der Tabellenblätter auslesen, Angaben zu den Tabellen in Folgespalten eintragen
'
Sheets(1).Select
iRow = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each cell In Worksheets(1).Range("B8:B" & iRow)
iOffsetColCounter = 5
cell.Offset(0, 1).Value = Worksheets(cell.Text).UsedRange.Columns.Count
cell.Offset(0, 2).Value = Worksheets(cell.Text).UsedRange.Rows.Count
cell.Offset(0, 3).Value = Worksheets(cell.Text).Cells.SpecialCells(xlCellTypeLastCell).Address

cell.Offset(0, iOffsetColCounter).Value = Worksheets(cell.Text).Range("B2")
iOffsetColCounter = iOffsetColCounter + 1

'cell.Offset(0, iOffsetColCounter).Value = Worksheets(cell.Text).Range("B3")
'iOffsetColCounter = iOffsetColCounter + 1

cell.Offset(0, 6).Value = Worksheets(cell.Text).Range("B4")
iOffsetColCounter = iOffsetColCounter + 1

'cell.Offset(0, 8).Value = Worksheets(cell.Text).Range("B5")
'iOffsetColCounter = iOffsetColCounter + 1

cell.Offset(0, 7).Value = Worksheets(cell.Text).Range("D6")
iOffsetColCounter = iOffsetColCounter + 1

On Error Resume Next
cell.Offset(0, 4).Value = Worksheets(cell.Text).Columns.Find("Bemerkung").Address
On Error GoTo 0
Next cell
'


Bild

Beiträge aus den Excel-Beispielen zum Thema " Zelladressen der Suchergebnisse auflisten"