Microsoft Excel

Herbers Excel/VBA-Archiv

Mit VBA Feld aus einer Summe markieren

Betrifft: Mit VBA Feld aus einer Summe markieren von: Axel Müller
Geschrieben am: 12.10.2007 10:30:36

Hallo,

ich habe folgende Aufgabe für euch :) (da ich sie nicht lösen kann):

Ich habe ein Tabellenblatt, in dem z.B. in Feld A1 steht: B2+B3+B4
nun möchte ich über VBA erreichen, dass diese Felder markiert werden. (B2,B3,B4 = rot)

Das ist das erste Problem, das zweite ist, dass es viele Felder sind (A1-A200), die alle unterschiedlich viele Summanden aufweisen. Aber diese werden nicht doppelt verwendet.
Ich muss also für alle 200 Felder unterschiedliche Farben erzeugen damit ich die b-Felder eindeutig zuordnen kann.

Habt ihr einen Tipp, wie ich das Problem lösen kann?

Gruß
Axel

  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Peter Feustel
Geschrieben am: 12.10.2007 10:44:10

Hallo Axel,

mehr als 56 Farben stehen dir aber nicht zur Verfügung.

Gruß Peter


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Andi
Geschrieben am: 12.10.2007 10:51:15

Hi,

hier mal ein Ansatz:



Sub t()
Dim c1 As Range
Dim c2 As Range
Dim farbe As Integer
farbe = 3
For Each c1 In Range("A:A")
If c1.HasFormula Then
  For Each c2 In c1.Precedents
    c2.Interior.ColorIndex = farbe
  Next c2
  farbe = farbe + 1
End If
Next c1
End Sub




Peter hat schon richtig angemerkt, dass mehr als 56 Farben nicht zur Verfügung stehen. Um schwarz und weiß zu umgehen, habe ich erst bei ColorIndex 3 angefangen, also nur 53.
Alternativ kannst Du vielleicht statt mit ColorIndex mit RGB-Farben arbeiten, da müsste man aber rumprobieren um 200 vernünftige Kontraste hinzubekommen.

Wäre es nicht vielleicht übersichtlicher, wenn die jeweiligen Zellen nur dann markiert würden, wenn die Zelle mit der Formel angeklickt wird?

Schönen Gruß,
Andi


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Andi
Geschrieben am: 12.10.2007 10:53:49

Hi nochmal,

der gleiche Ansatz, nur weniger umständlich... ;-))

Sub t()
Dim c1 As Range
Dim farbe As Integer
farbe = 3
For Each c1 In Range("A:A")
If c1.HasFormula Then
  c1.Precedents.Interior.ColorIndex = farbe
  farbe = farbe + 1
End If
Next c1
End Sub



Schönen Gruß,
Andi


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Axel Müller
Geschrieben am: 12.10.2007 11:01:30

Hallo Peter und Andi,

das mit den 56 Farben wusste ich nicht, danke für die Info.

Ich habe mir jetzt überlegt, dass ich auch einfach eine Nummer, oder den Text aus einem bestimmten Feld in die Spalte hinter den jeweiligen Werten schreiben will, statt sie zu markieren.

Als Beispiel:
A1=B1+B2+B3
dann steht in C1=1 und C2=1 und C3=1


Wisst ihr wie ich das hinbekomme?

Gruß
Axel


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Tino
Geschrieben am: 12.10.2007 10:54:37

Hallo,
gehts so in etwa

Sub Makro1()
Dim Bereich As Range, Zelle As Range
Dim Farbe As Variant
Farbe = 2
Cells.Interior.ColorIndex = xlNone
Set Bereich = Cells.SpecialCells(xlCellTypeFormulas, 1)
For Each Zelle In Bereich
Farbe = Farbe + 1
Zelle.Interior.ColorIndex = Farbe
Next Zelle
End Sub



Gruß
Tino


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Andi
Geschrieben am: 12.10.2007 10:59:35

Hi,

er möchte aber nicht die Zellen mit Formel bunt haben, sondern die Zellen, auf die sich die Formeln beziehen...

Schönen Gruß,
Andi


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Axel Müller
Geschrieben am: 12.10.2007 11:03:22

Mh, ihr wart irgendwie schneller als ich mit dem schreiben :D

Im vorherigen Txt steht meine neueste Idee ;)

Gruß
Axel


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Worti
Geschrieben am: 12.10.2007 11:11:48

Hallo Axel,
was hälst du hiervon:

Sub Axel()

    Dim c As Range
    Dim lngZeile As Long
    
    lngZeile = 1
    For Each c In Range("A1:A10") 'Bereich anpassen
        If c.HasFormula Then
           Cells(lngZeile, "C").Value = "Formel in " & c.Address(False, False) & " bezieht sich  _
auf " _
           & c.Precedents.Address(False, False)
           lngZeile = lngZeile + 1
        End If
    Next c
    
End Sub



Gruß Worti


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Axel Müller
Geschrieben am: 12.10.2007 11:32:16

Hallo Worti,
danke für deinen Ansatz.

Die Formel ist etwas verdreht, ich weiß nicht wie ich es besser ausdrücken soll.

Ich möchte nicht hinter A1 stehen haben, auf welche Felder es alles verweist,
sondern ich möchte hinter den Felder, auf die verwiesen wird, stehen haben, dass auf sie von Feld A1 verwiesen wurde.

Beispiel:

A1=B1+B2+B3
C1 = "A1"
C2 = "A1"
C3 = "A1"

Ich hoffe man versteht wie ich das meine :)

Gruß
Axel


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Andi
Geschrieben am: 12.10.2007 11:36:11

Hi,

so?

Sub t()
Dim c1 As Range
For Each c1 In Range("A:A")
If c1.HasFormula Then
  c1.Precedents.Offset(0, 1) = c1.Address(0, 0)
End If
Next c1
End Sub



Schönen Gruß,
Andi


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Axel Müller
Geschrieben am: 12.10.2007 11:43:48

Hallo Andi,

ganz genau so :D

Vielen Dank an alle!

gruß
Axel


  

Betrifft: Gern geschehen... (ot) von: Andi
Geschrieben am: 12.10.2007 11:57:29

.


  

Betrifft: Und viel schöner als meins :-) oT von: Worti
Geschrieben am: 12.10.2007 12:06:41

Gruß Worti


  

Betrifft: AW: aber mit kleinem Schönheitsfehler von: Daniel
Geschrieben am: 13.10.2007 13:54:06

Hi
hat aber nen kleinen Nachteil
wird eine Zelle von mehren Zellen referenziert, siehst du nur den Zellbezug zur letzten Zelle, weil die Werte immer überschrieben werden.
um also rauszufinden , ob eine Zelle doppelt referenziert wird (z.B. Fehlersuche, wenn Summen nicht stimmen), ist dieser Code ungeeignet.

Falls du aber sowas benötigst, schau mal an anderer Stelle im Threas, da hab ich nen Code reingestellt, der dies berücksichtigt.

Gruß, Daniel


  

Betrifft: AW: aber mit kleinem Schönheitsfehler von: Andi
Geschrieben am: 15.10.2007 09:00:59

Hi,

Axel schrieb aber:
Das ist das erste Problem, das zweite ist, dass es viele Felder sind (A1-A200), die alle unterschiedlich viele Summanden aufweisen. Aber diese werden nicht doppelt verwendet.

Schönen Gruß,
Andi


  

Betrifft: AW: aber mit kleinem Schönheitsfehler von: Daniel
Geschrieben am: 15.10.2007 19:32:15

Hi

kann sein es so ein sollte, was aber nicht bedeutet, das es immer so ist.
wenns mal nicht so ist, fällts mit meinem Code halt sofort auf, und das wäre dann ja ein zusätzlicher Vorteil.
Da ich vermute, daß ein solcher Code in erster Linie zur Analyse und Fehlersuche in der Datei verwendt wird, sicherlich keine schlechte Sache.

Gruß, Daniel


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Jana H.
Geschrieben am: 12.10.2007 13:21:35

Hallo Andi,

ich habe ein ähnliches Problem, allerdings liegen die "Vorgänger-Daten" in einem anderen Tabellenblatt. Würde das funktionieren?

Danke im Voraus ;)

Gruß
Jana


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Andi
Geschrieben am: 12.10.2007 13:39:03

Hi,

sorry, da weiß ich leider nicht weiter.
Die Precedents-Eigenschaft funktioniert nur innerhalb einer Tabelle, und mir ist leider kein workaround bekannnt...

Ich lass die Frage mal offen, vielleicht hat jemand anderes eine Idee dazu.

Schönen Gruß,
Andi


  

Betrifft: AW: für Zellbezüge auf andere Blätter von: Daniel
Geschrieben am: 13.10.2007 14:01:06

Hi
als Workaround wäre folgedes Denkbar:

- Formelstring durchsuchen, wenn ein "!" gefunden wird, enthält die Zelle einen Bezug auf ein anderes Tabellenlatt
- vom Ausrufezeichen nach links bis zum nächsten "(" oder ";" ist Blattname des Zellbezugs, vom Ausrufezeichen nach rechts bis zum nächsten ")" oder ";" ist dann die Addresse

mit diesen Beiden Informationen kann das Makro dann die Zelle im anderen Blatt anspringen und dort die Information ablegen.

ist halt ein bischen Programmieraufwand, den Formelstring zu analysieren.
Außdem findest du so auch nur direkt angegeben Zellebezüge, bei Namen wirds dann wieder schwierig.

Gruß, Daniel


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Worti
Geschrieben am: 12.10.2007 11:51:06

Hallo Axel,
wenn keine Bereichsadressen in den Formeln sind, so:

Sub Axel()

    Dim c As Range
    Dim adressen
    Dim intAnz As Integer
    Dim lngNummer As Long
    
    For Each c In Range("A1:A3")
        If c.HasFormula Then
           adressen = Split(c.Precedents.Address(True, False), ",")
           For intAnz = 0 To UBound(adressen)
                lngNummer = CLng(Mid(adressen(intAnz), InStr(1, adressen(intAnz), "$") + 1))
                Cells(lngNummer, "C").Value = c.Row
           Next intAnz
        End If
    Next c
    
End Sub


Gruß Worti


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Worti
Geschrieben am: 12.10.2007 11:52:56

Hallo Axel,
wenn keine Bereichsadressen in den Formeln sind, so:

Sub Axel()

    Dim c As Range
    Dim adressen
    Dim intAnz As Integer
    Dim lngNummer As Long
    
    For Each c In Range("A1:A3")
        If c.HasFormula Then
           adressen = Split(c.Precedents.Address(True, False), ",")
           For intAnz = 0 To UBound(adressen)
                lngNummer = CLng(Mid(adressen(intAnz), InStr(1, adressen(intAnz), "$") + 1))
                Cells(lngNummer, "C").Value = c.Row
           Next intAnz
        End If
    Next c
    
End Sub


Gruß Worti


  

Betrifft: AW: Mit VBA Feld aus einer Summe markieren von: Daniel
Geschrieben am: 13.10.2007 12:37:41

Hi
dann probier mal das hier aus.
wird eine Zelle von mehren anderen refernziert, so wird der zusätzliche Zellbezug angehängt.

die Zellen hinter den addressierten Zellen sollten allerdings leer sein, denn die werden gnadenlos überschrieben.
Falls das ein Problem ist, müsste man das alles in einen Kommentar reinschreiben, dann wärs kein Problem

Sub test()
Dim Zelle1 As Range
Dim Zelle2 As Range

For Each Zelle1 In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    For Each Zelle2 In Zelle1.Precedents
            With Zelle2.Offset(0, 1)
                If InStr(.Value, Zelle1.Address) = 0 Then
                    .Value = .Value & Zelle1.Address(0, 0) & ";"
                End If
            End With
    Next
Next

End Sub



Gruß, Daniel

du kannst das Makro auch mehrfach laufen lassen, ist der Zellbezug schon vorhanden, wird er nicht doppelt reingeschrieben.


 

Beiträge aus den Excel-Beispielen zum Thema "Mit VBA Feld aus einer Summe markieren"