Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen

Farbige Zellen suchen und Zuordnung auflisten

Betrifft: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 06:47:26

Hallo zusammen,
ich hab nicht so die Ahnung von Makros und Automationen im Excel, bräuchte allerdings "dringend" eine Vereinfachung meines Excelchaos.
es dreht sich um folgende Problematik:
ich muss diverse Dienstpläne in unsere Personalplanung einarbeiten, leider liefert eine Abteilung ihren Plan in einem völlig anderen Format, wodurch ein einfaches 'rüberkopieren nicht möglich ist.
Darin sind die Dienste in einer Matrix dargestellt: Spalte A enthält die Namen der Mitarbeiter und Zeile 1 die Kalenderwochen. Die Zuordnung der Dienste erfolgt dann durch ein einfärben der entsprechenden Zelle.
Das sieht zwar schick aus, aber unser System ist halt einer simple Aneinanderreihung der Dienste bzw. Mitarbeiten
(Zeile 1: Kalenderwochen/ Zeile 2: Mitarbeiter).
kann man mit Hilfe eines Makros das Matrixsystem in unsere Anzeigeform bringen?

vielen Dank im voraus
LG Heike

  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 07:31:26

zum besseren Verständnis habe ich mal eine Beispielmappe gebastelt:

https://www.herber.de/bbs/user/54433.xls

Schönen Dank
LG Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Heiko S.
Geschrieben am: 08.08.2008 08:21:39

Hallo Heikoe,

vom Prinzip so wie in dem Makro was nun in deiner Beispeildatei drin ist.

Tabelle Ist Zustand aktivieren Makro Neu-Sortieren starten fertig.


https://www.herber.de/bbs/user/54434.xls


Alles andere müßtest du mal genauer beschrieben.
Also:
Wo kommen die Daten genau her.
Ist diese datei mit den Daten schon offen.
Hat die datei mit den Daten immer den gleichen Namen.
Soll der "Soll-Zustand" immer in eine neue Tabelle oder an eine bestehende angehängt werden.
...
...
...




Gruß Heiko

PS: Rückmeldung wäre nett !


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 08:40:34

Hey Heiko,

super, danke. ich bin schon mal begeistern. Das was ich bisher sehe, ist schon mal sehr genial.
Zu deinen Fragen: Die Datei wird im Firmennetz auf dem Dokumentensystem bereitgestellt (Weboberfläche).
Ob der Dateinamen immer gleich ist, kann ich zwar nicht mit 100%iger Sicherheit sagen (bei Tabbezeichnung ist es garantiert so), aber ich setze es mal voraus, da man sonst firmenweit Probleme mit der Verlinkung bekommen würde. Um ein Herunterladen werde ich wohl nicht drum rum kommen, also kann ich sie ja auch gleich öffnen.
Der Soll-Zustand soll aus der gelieferten Ist-Excel (Abteilung.xls Tab "2008") in die bestehende Soll-Excel (Planung.xls; neuer Tab "Abteilung") in einen neuen Tab.
Vielen Dank noch mal....
Grüße Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Heiko S.
Geschrieben am: 08.08.2008 09:05:07

Hallo Heike,

dann so:

Ich gehe davon aus das die Datei Abteilung.xls offen ist.
Ich gehe davon aus das der VBA Code in der Datei Planung.xls hinterlegt wird.
Und ich habe die Datei und Tabellennamen genommen die du angegeben hast.

Schön machen, also Spaltenbreite, Rahmen, Schriftart, Fett usw. kann man natürlich auch automatisieren. Das solltest du aber erstmal selbst probieren wenn es nötig ist, sonst nachfragen.

Sub NeuSortieren()
Dim bytI As Byte, bytLastC As Byte
Dim lngI As Long, lngLastRow As Long
Dim wksQuelle As Worksheet, wksZiel As Worksheet

' Hier kannst du dann den Datei und Tabellen Namen anpassen !!!
Set wksQuelle = Workbooks("Abteilung.xls").Worksheets("2008")

With ThisWorkbook
    bytLastC = wksQuelle.Cells(1, 255).End(xlToLeft).Column
    lngLastRow = wksQuelle.Cells(65535, 1).End(xlUp).Row
    
    Set wksZiel = .Worksheets.Add
    wksZiel.Name = "Abteilung"
    
    For bytI = 2 To bytLastC
        wksZiel.Cells(1, bytI) = wksQuelle.Cells(1, bytI)
        wksZiel.Cells(1, bytI).Font.Bold = True
        For lngI = 2 To lngLastRow
            If wksQuelle.Cells(lngI, bytI).Interior.ColorIndex <> xlNone Then
                wksZiel.Cells(2, bytI) = wksQuelle.Cells(lngI, 1)
                Exit For
            End If
        Next lngI
    Next bytI
End With
wksZiel.Cells(1, 1) = "Kalenderwoche"
wksZiel.Cells(2, 1) = "Mitarbeiter"

End Sub





Gruß Heiko

PS: Rückmeldung wäre nett !


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 09:26:39

Hey Heiko (mein Retter ;o) )

noch mal tausend dank. hat soweit alles geklappt. wegen schick machen, kann ich ja vielleicht ein makro aufzeichnen. das dürfte ich ja wohl hinbekommen.

ich wünsch dir noch nen schönen tag
vLG Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 11:04:11

ich bin's schon wieder ;o)

Mir ist da da eine wichtige Gegebenheit durch die Finger gerutscht. In der Beispielmappe funktioniert es ja ganz toll. Ich hatte nur etwas nicht bedacht: ab dieser Woche taucht in der Zuarbeit der Abteilung ein Unterbereich auf. Soll heißen, dass es zwei Matrixen (ist das die Mehrzahl?) untereinander auf. Das Makro listet mir aber nur die Treffer der ersten Matrix auf.

ich verwende man nur zur optischen Verdeutlichung mein bereits genutztes Bespiel:
https://www.herber.de/bbs/user/54442.xls

Sorry.

Gruß Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Heiko S.
Geschrieben am: 08.08.2008 15:11:27

Hallo Heike,

dann so mit ein wenig manuellen Input:
Bitte bei der Abfrage der Trennzeile die Zeile im Tabellenblatt mit der Maus markieren die die Trennlinie zwischen den beiden Abteilungen darstellt. Diese Abfrage läßt sich bestimmt noch automatisieren, aber nur dann wenn man weiß ob es immer eine Leerzeile oder doch zwei oder gar keine zwischen den Abteilungen gibt. Will sagen man muß mehr Rahmenbedingungen kennen um alles zu berücksichtigen.

Sub NeuSortieren2()
Dim bytI As Byte, bytLastC As Byte
Dim lngI As Long, lngLastRow As Long, lngTrennZeile As Long
Dim wksQuelle As Worksheet, wksZiel As Worksheet
Dim rngTrennZeile As Range

' Hier kannst du dann den Datei und Tabellen Namen anpassen !!!
Set wksQuelle = Workbooks("Abteilung.xls").Worksheets("2008")

bytLastC = wksQuelle.Cells(1, 255).End(xlToLeft).Column
lngLastRow = wksQuelle.Cells(65535, 1).End(xlUp).Row

Workbooks("Abteilung.xls").Activate
Set rngTrennZeile = Application.InputBox("Markieren Sie bitte die 'Trennzeile' zwischen den  _
Abteilungen", " Trennzeile", Type:=8)
lngTrennZeile = rngTrennZeile.Row

Set wksZiel = ThisWorkbook.Worksheets.Add
wksZiel.Name = "Abteilung"

For bytI = 2 To bytLastC
    wksZiel.Cells(1, bytI) = wksQuelle.Cells(1, bytI)
    wksZiel.Cells(1, bytI).Font.Bold = True
    wksZiel.Cells(3, bytI) = wksQuelle.Cells(1, bytI)
    wksZiel.Cells(3, bytI).Font.Bold = True
    For lngI = 2 To lngLastRow
        If wksQuelle.Cells(lngI, bytI).Interior.ColorIndex <> xlNone Then
            If wksQuelle.Cells(lngI, bytI).Row > lngTrennZeile Then
                wksZiel.Cells(4, bytI) = wksQuelle.Cells(lngI, 1)
            Else
                wksZiel.Cells(2, bytI) = wksQuelle.Cells(lngI, 1)
            End If
        End If
    Next lngI
Next bytI
wksZiel.Cells(1, 1) = "Kalenderwoche Abteilung 1"
wksZiel.Cells(2, 1) = "Mitarbeiter"
wksZiel.Cells(3, 1) = "Kalenderwoche Abteilung 2"
wksZiel.Cells(4, 1) = "Mitarbeiter"

End Sub




Gruß Heiko

PS: Rückmeldung wäre nett !


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 09.08.2008 07:52:20

schönen guten morgen...

und natürlich auch erst mal nen lieben dank.

im prinzip würde die formatierung immer so aussehen, wie ich sie als letzes hoch geladen habe (eine leerzeile, dann die abteilungsbezeichnung, was wohl auch als leerzeile zu werten ist und dann beginnt die aufzählung der mitarbeiter).
ich vermute mal, dass denmächst weitere abteilungen unten angehängt werden, daher mal ne blöde frage: kann man das nicht vielleicht so abfragen, dass einfach: von oben nach unten (zum beipiel zeile 120 als grenze) alle farbigen zellen der spalte erfasst und dann die "gematchten" mitarbeiter in der soll-tabelle untereinander, zugeordnet zur kw, auflistet? Die Reihenfolge der Abteilungen in der ist-tabelle würde gleich bleiben (soviel ist sicher)...so könnte man doch dies in der bestehenden soll-tabelle schon vorgeben und setzt dann die treffer nur dahinter.
sorry, ist wohl etwas konfus geworden...und scheinbar habe ich bei den automatisierungsmöglichkeiten blut gelegt und die bodenhaftung verloren.
ich mache noch mal ne datei zur verdeutlichung.

https://www.herber.de/bbs/user/54466.xls

vielen dank und lieben gruß

Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Erich G.
Geschrieben am: 10.08.2008 16:41:02

Hi Michael,
probier mal

Option Explicit

Sub NeuSortieren2()
   Dim lngC As Long, lngSp As Long, lngR As Long, lngZe As Long
   Dim lngAbt As Long, arrE() As String, lngE As Long

   With Worksheets("Ist-Zustand")
      lngZe = .Cells(.Rows.Count, 1).End(xlUp).Row
      lngSp = .Cells(1, .Columns.Count).End(xlToLeft).Column
      lngAbt = WorksheetFunction.CountBlank(Range(Cells(1, 1), Cells(lngZe, 1))) + 2
      ReDim arrE(1 To lngAbt, 1 To lngSp)

      arrE(1, 1) = "Kalenderwoche"
      arrE(2, 1) = .Cells(1, 1)
      lngE = 2
      For lngC = 2 To lngSp
         arrE(1, lngC) = .Cells(1, lngC)
      Next lngC
      For lngR = 2 To lngZe
         For lngC = 2 To lngSp
            If IsEmpty(.Cells(lngR, 1)) Then
               lngE = lngE + 1
               arrE(lngE, 1) = .Cells(lngR + 1, 1)
               lngR = lngR + 2
            End If
            If .Cells(lngR, lngC).Interior.ColorIndex <> xlNone Then _
               arrE(lngE, lngC) = .Cells(lngR, 1)
         Next lngC
      Next lngR
   End With

   Worksheets.Add.Name = "Soll-Zustand1"
   Range(Cells(1, 1), Cells(lngAbt, lngSp)) = arrE
   Rows(1).Font.Bold = True
   Cells.HorizontalAlignment = xlCenter
   Columns(1).AutoFit
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 10.08.2008 23:22:21

Hey Erich,

das schaut super aus und trifft es auf den Punkt.

schönen Dank für deine Hilfe

Gruß Heike


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Beverly
Geschrieben am: 08.08.2008 08:28:15

Hi Heike,

dein Matrixübertrag ist nicht ganz richtig: in KW03 muss MA5 und in KW04 muss MA03 (zumindest steht es so in der Ausgangstabelle in "Ist-Zustand")

Sub transponieren()
    Dim inSpalte As Integer
    Dim inZeile As Integer
    Dim wsTabelle1 As Worksheet
    Set wsTabelle1 = Worksheets("Ist-Zustand")
    With Worksheets("Soll-Zustand")
        For inSpalte = 2 To IIf(IsEmpty(.Cells(1, .Columns.Count)), .Cells(1, .Columns.Count). _
End(xlToLeft).Column, .Columns.Count)
            For inZeile = 2 To IIf(IsEmpty(.Cells(1, .Columns.Count)), .Cells(1, .Columns.Count) _
.End(xlToLeft).Column, .Columns.Count)
                If wsTabelle1.Cells(inZeile, inSpalte).Interior.ColorIndex = 10 Then
                    .Cells(2, inSpalte) = wsTabelle1.Cells(inZeile, 1)
                    Exit For
                End If
            Next inZeile
        Next inSpalte
    End With
End Sub



GrußformelBeverly's Excel - Inn


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Excel-Opfer
Geschrieben am: 08.08.2008 08:56:04

sorry, war vielleicht etwas missverständlich. Die "ist" und "soll" haben nicht den gleichen Datenstand. diese sollten nur zur visuellen Verdeutlichung dienen


  

Betrifft: AW: Farbige Zellen suchen und Zuordnung auflisten von: Beverly
Geschrieben am: 08.08.2008 09:24:31

Hi Heike,

auch wenn sie nicht den gleichen Bestand haben funktioniert der Code trotzdem, wenn man davon ausgeht, dass in deinem "Soll-Zustand" die Kalenderwochen in Zeile 1 bereits vorgetragen sind - es stand nichts davon in deinem Beitrag, dass ein neues Tabellenblatt generiert werden soll.


GrußformelBeverly's Excel - Inn


  

Betrifft: AW: Farbige Zellen zuordnen (ohne VBA) von: Erich G.
Geschrieben am: 08.08.2008 08:43:45

Hallo Heike,
ohne VBA gehts natürlich auch. Schau dir das mal an:
https://www.herber.de/bbs/user/54437.xls

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Farbige Zellen zuordnen (ohne VBA) von: Excel-Opfer
Geschrieben am: 08.08.2008 09:23:05

guten morgen Erich,

danke erst mal. ich bin überrascht, dass auch ohne vba möglich ist. deine lösung ist auch ne nette variante, auch wenn mich wundert, warum kw6 ein mitarbeiter gefunden wurde. an sich hätte ich aber das "problem" bzw die faulheit (*hoho*), dass ich noch in der stammdatentabelle rumeditieren müsste.
trotzdem danke

LG Heike


 

Beiträge aus den Excel-Beispielen zum Thema "Farbige Zellen suchen und Zuordnung auflisten"