Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1552to1556
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

Zellenformatierung

Zellenformatierung
16.04.2017 18:52:54
Frank

Tach zusammen,
habe z.B in Tabellenblatt 1 Zelle B1 folgendes stehen:
Max Mustermann(Zeilenumbruch)
Musterstraße 11, 47111 Musterstadt
auf diese Zelle wird aus Tabellenblatt 2 zugegriffen.Nun möchte ich aber,das "Max Mustermann" hier nicht zu sehen ist.Selbst den Teil mit weißer Schriftfarbe zu formatieren bringt nicht den gewünschten Erfolg.
Wer kann Abhilfe schaffen?
Lg

34
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellenformatierung
16.04.2017 19:04:34
Sepp
Hallo Frank,
meinst du so?
AW: Zellenformatierung
16.04.2017 19:15:25
Frank
Hallo Sepp,
genauso hab ich mir das vorgestellt,perfekt.Kann ich diese Formel nun auf jeden anderen namen anwenden,unabhängig der Länge des Namens?Die Zelle muss natürlich angepasst werden.
Lg Frank
AW: Zellenformatierung
16.04.2017 19:31:55
Sepp
Hallo Frank,
ja sicher, es wird immer der Text NACH dem Zeilenumbruch ausgegeben.
Gruß Sepp
AW: Zellenformatierung
16.04.2017 19:38:06
Frank
Hallo Sepp,habs grad beim Testen raus gefunden.Natürlich hab ich mich mal wieder zu früh gefreut,weil ich bei deiner Lösung mit einer Hilfsspalte arbeiten muss,da ich ja sonst die Zelle nicht mit =Adressen!A1 etc. ansprechen kann,weil die Position immer variiert.
Aber dein Tip ist die perfekte Lösung.Vielen Dank dafür
LG Frank
P.S. hätte ja noch son paar Kleinigkeiten auf Lager,weiß aber nicht,ob ich dafür jeweil nen neuen Thread aufmachen muss.
Anzeige
AW: Zellenformatierung
16.04.2017 19:40:38
Sepp
Hallo Frank,
wieso Hilfsspalte?
Deshalb ist eine aussagekräftige Beispieldatei meist hilfreich.
Gruß Sepp
AW: Zellenformatierung
16.04.2017 19:51:42
Sepp
Hallo Frank,
und von wo sollen die Daten genommen werden?
Gruß Sepp
AW: Zellenformatierung
16.04.2017 20:21:03
Frank
Hey Sepp,
die Daten werden aus dem Tabellenblatt "Dauerfahrten" genommen,sind aber in den anderen Tabellenblätter immer an anderen Stellen.
Lg Frank
AW: Zellenformatierung
16.04.2017 20:29:25
Sepp
Hallo Frank,
der Code in der Datei verrät mir, dass da noch mehr dahinter steckt.
Was willst du den am Ende erreichen?
Ich finde den Tabellenaufbau der gesamten Datei eher ungünstig.
Wieso wird für jedes Datum ein separates Blatt benötigt? Eine Liste in der Die Daten Chronologisch eingetragen werden würde das Ganze sehr vereinfachen.
Warum stehen Name und Adresse in einer Zelle, wenn man sie nachher wieder aufdröseln muss?
Wenn man die Daten in Ordentlichen Listen führt, braucht man sich bei der Auswertung nicht "verrenken"!
Gruß Sepp
Anzeige
AW: Zellenformatierung
16.04.2017 20:41:50
Frank
Hi Sepp,
in der Datei gibts ein Monatsblatt und ggf 31 Tagesblätter,da in den Tagesblättern mitunter bis zu 25 Fahrten stehen können und die fürs Finanzamt sind und die die Namen nicht zu interessieren hat.Im Tabellenblatt Dauerfahrten sind immer wiederkehrende Touren hinterlegt.Der Code den du da noch siehst,ist ein SQL Code,der abfragen soll,ob es zwischen der KM-Angabe von Dauerfahrten zu den Datumsblättern ein Unterschied gibt.Leider passte der nicht so genau.
Leider kann ich sowas schriftlich nicht immer konkret beschreiben,gerne auch per Telefon wenn du Interesse hast,ist bedeutend einfacher für mich und wahrscheinlich auch für dich.
Hier mal die Original-Datei:

Die Datei https://www.herber.de/bbs/user/112913.xls wurde aus Datenschutzgründen gelöscht


Lg Frank
P.S. Meine Telenummer 02041/3911250
Anzeige
AW: Zellenformatierung
16.04.2017 20:53:50
Sepp
Hallo Frank,
OK, aber in der Datei sollten auch ein paar gefüllte Tagesblätter sein.
Zur Problemlösung ist das Forum da, es sollen ja auch andere evtl. davon profitieren können.
Gruß Sepp
noch was
16.04.2017 20:55:40
Sepp
Hallo nochmal,
sind die Tagesblätter immer nach dem Kalendertag benannt, oder wie in der zweiten Datei mit "Tagesblatt X"?
Gruß Sepp
AW: noch was
16.04.2017 21:18:49
Frank
Das Tagesblatt wird per Schaltfläche kopiert,dann versteckt und ich gebe in B1 das Datum ein,welches ich benötige.Wegen der gefüllten Datei schau ich direkt mal
AW: noch was
16.04.2017 21:31:12
Sepp
Hallo Frank,
ohne unnötige SQL-Abfrage.
Es werden nur die Dauerfahrten aufgelistet bei denen die Km nicht den Vorgaben entsprechen.
https://www.herber.de/bbs/user/112914.xls
Gruß Sepp
Anzeige
neuer Code
16.04.2017 21:35:17
Sepp
Hallo Frank,
hab jetzt erst bemerkt, dass die Blätter doch nach dem Datum benannt werden.
Sub check2()
Dim objSht As Worksheet, objChk As Worksheet, objRides As Worksheet
Dim vntRet1 As Variant, vntRet2 As Variant
Dim lngNext As Long, lngI As Long, lngLast As Long

Set objChk = Sheets("Check")
Set objRides = Sheets("Dauerfahrten")

objChk.Range("A2:E1000").ClearContents
lngNext = 1

For Each objSht In ThisWorkbook.Worksheets
  If IsDate(objSht.Name) Then
    With objSht
      lngLast = Application.Max(5, .Cells(Rows.Count, 2).End(xlUp).Row)
      For lngI = 5 To lngLast
        If .Cells(lngI, 2) <> "" And .Cells(lngI, 3) <> "" Then
          vntRet1 = Application.Match(.Cells(lngI, 2), objRides.Columns(2), 0)
          If IsNumeric(vntRet1) Then
            vntRet2 = Application.Match(.Cells(lngI, 3), objRides.Columns(3), 0)
            If IsNumeric(vntRet2) Then
              If vntRet1 = vntRet2 Then
                If .Cells(lngI, 4) <> objRides.Cells(vntRet1, 4) Then
                  lngNext = lngNext + 1
                  objChk.Cells(lngNext, 1) = .Cells(1, 2)
                  objChk.Hyperlinks.Add _
                    Anchor:=objChk.Cells(lngNext, 2), _
                    Address:="", _
                    SubAddress:="'" & .Name & "'!" & .Cells(lngI, 2).Address, _
                    TextToDisplay:=Mid(.Cells(lngI, 2), InStr(1, .Cells(lngI, 2), Chr(10)) + 1)
                  objChk.Cells(lngNext, 3) = Mid(.Cells(lngI, 3), InStr(1, .Cells(lngI, 3), Chr(10)) + 1)
                  objChk.Cells(lngNext, 4) = objRides.Cells(vntRet1, 4)
                  objChk.Cells(lngNext, 5) = .Cells(lngI, 4)
                End If
              End If
            End If
          End If
        End If
      Next
    End With
  End If
Next

End Sub

Gruß Sepp
Anzeige
AW: neuer Code
16.04.2017 22:08:42
Frank
Jetzt hast du mich im ersten Moment ein wenig irritiert,weil zunächst der Code aus der Beispieldatei funzte und der Nachfolgecode gar nicht.habs aber angepasst wegen dem Datum.Funktioniert soweit auch,nur zeigt er mir in Check!E2 ne 12,2 und ich weiß nicht,woher er sich die jetzt holt.Muss deinen Code in Ruhe an meine Datei anpassen,weil in deiner geht alles perfekt. Eine Bitte hätte ich noch,vorrausgesetzt,das ist kein zu großer Aufwand:
Spalte E(Km ist) eine Spalte nach links und dann in Spalte E eventuell der Vermerk welche Zeile es ist,da einige Dauerfahrten auch täglich mehrmals vorkommen können,ansonsten ist es genau das,was ich gesucht habe.Absolut perfekt "Daumen hoch"
Anzeige
AW: neuer Code
16.04.2017 22:11:55
Sepp
Hallo Frank,
die 12,2 hab ich geschrieben, sonst wäre ja kein Fehler vorhanden!
Die Zeile brauchst du nicht, in Spalte B ist ein Hyperlink hinterlegt, mit dem du direkt in die entsprechende Zeile des betroffenen Blatte springen kannst.
Gruß Sepp
AW: neuer Code
16.04.2017 22:31:22
Frank
Hallo Sepp,
das ist natürlich genial und meine Idee überflüssig.Ich dachte der Hyperlink kämme durch meine vielen geöffneten Dateien zustande. Aber irgendwie will dein Code in meiner Datei mit schon bestehenden Datumsblättern überhaupt nicht funktionieren.Habe schon allen anderen überflüssigen Mist an Codes entfernt.
Warum das so ist,kann ich grad nicht sagen,denn in deiner Datei klappt alles einwandfrei. Bezüglich der Teilformatierung weißt du nun sicherlich,was ich mit der Hilfsspalte meinte oder?
Lg Frank
Anzeige
AW: neuer Code
16.04.2017 23:15:00
Frank
Hallo Sepp,
habe es jetzt hinbekommen,funzt wie bei dir.Dazu habe ich aber noch ne Frage:
1. Kann es sein,das deine Lösung die Rücktour(Von C nach B) nicht automatisch berücksichtigt und ich diese Tour ebenfalls in die Dauerfahrten eintragen muss?
2. Habe folgenden Code gefunden,der mir die aktive Zeile farbig markieren soll,zu der der Hyperlink springt:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.UsedRange.EntireRow.Interior.ColorIndex = xlNone
Selection.EntireRow.Interior.ColorIndex = 6 'gelb
End Sub
Diesen hab ich zu Testzwecken in das Blatt 25.März eingetragen,funzt aber leider nicht.
Lg Frank
Anzeige
AW: neuer Code
17.04.2017 08:07:44
Sepp
Hallo Frank,
die Rückfahrten hab ich gar nicht bedacht ;-)
Mit diesem Code werden auch diese berücksichtigt.
Sub check2()
Dim objSht As Worksheet, objChk As Worksheet, objRides As Worksheet
Dim lngNext As Long, lngI As Long, lngLast As Long, lngC As Long, lngE As Long
Dim bolDone As Boolean

Set objChk = Sheets("Check")
Set objRides = Sheets("Dauerfahrten")

With objRides
  lngE = Application.Max(5, .Cells(.Rows.Count, 2).End(xlUp).Row)
End With

objChk.Range("A2:E1000").ClearContents
lngNext = 1

For Each objSht In ThisWorkbook.Worksheets
  If IsDate(objSht.Name) Then
    With objSht
      lngLast = Application.Max(5, .Cells(Rows.Count, 2).End(xlUp).Row)
      For lngI = 5 To lngLast
        bolDone = False
        If .Cells(lngI, 2) <> "" And .Cells(lngI, 3) <> "" Then
          For lngC = 5 To lngE
            If objRides.Cells(lngC, 2) <> "" And objRides.Cells(lngC, 3) <> "" Then
              If ((.Cells(lngI, 2) = objRides.Cells(lngC, 2) And .Cells(lngI, 3) = objRides.Cells(lngC, 3)) Or _
                (.Cells(lngI, 2) = objRides.Cells(lngC, 3) And .Cells(lngI, 3) = objRides.Cells(lngC, 2))) And _
                .Cells(lngI, 4) <> objRides.Cells(lngC, 4) Then
                lngNext = lngNext + 1
                objChk.Cells(lngNext, 1) = .Cells(1, 2)
                objChk.Hyperlinks.Add _
                  Anchor:=objChk.Cells(lngNext, 2), _
                  Address:="", _
                  SubAddress:="'" & .Name & "'!" & .Cells(lngI, 2).Address, _
                  TextToDisplay:=Mid(.Cells(lngI, 2), InStr(1, .Cells(lngI, 2), Chr(10)) + 1)
                objChk.Cells(lngNext, 3) = Mid(.Cells(lngI, 3), InStr(1, .Cells(lngI, 3), Chr(10)) + 1)
                objChk.Cells(lngNext, 4) = objRides.Cells(lngC, 4)
                objChk.Cells(lngNext, 5) = .Cells(lngI, 4)
                Exit For
              End If
            End If
          Next
        End If
      Next
    End With
  End If
Next

End Sub

Zum Färben der aktiven Zeile nutze diesen Code (kommt in das Modul "Diese Arbeitsmappe")
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
  If IsDate(.Name) Then
    .Cells.Interior.ColorIndex = xlNone
    If Target.Column < 5 Then
      .Range(.Cells(Target.Row, 1), .Cells(Target.Row, 4)).Interior.ColorIndex = 6
    End If
  End If
End With
End Sub


Gruß Sepp
Anzeige
AW: neuer Code
17.04.2017 14:19:06
Frank
Hallo Sepp,
vielen Dank vorab für deine Mühe.Leider muss ich dir mitteilen,das der neue Code nicht funktioniert bzw nicht das gewünschte Ergebnis liefert.Hab den Code getestet und folgendes festgestellt:
Nun zeigt der Code mir zwar die Rücktour an,auch wenn sie "nicht" in "Dauerfahrten" hinterlegt ist,dafür fehlt quasi die Hintour,zumindest bei mir.
Was die Einfärbung betrifft,da bin ich auch fündig geworden,auch wenns nicht ganz so aussieht wie bei dir:
'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Static AlteZelle As Range
'If Not AlteZelle Is Nothing Then
'AlteZelle.EntireRow.Interior.ColorIndex = xlColorIndexNone
'End If
'Target.EntireRow.Interior.ColorIndex = 15
'Set AlteZelle = Target
'End Sub
Bei meinem Code wird die ganze Zeile eingefärbt.
Lg Frank
AW: neuer Code
17.04.2017 14:22:56
Sepp
Hallo Frank,
lade mal deine Test-Datei hoch, das wird sonst zum Ratespiel.
Gruß Sepp
AW: neuer Code
17.04.2017 14:43:39
Frank
Hi Sepp,
hier ist sie schon:

Die Datei https://www.herber.de/bbs/user/112925.xls wurde aus Datenschutzgründen gelöscht


Lg
AW: neuer Code
17.04.2017 14:59:52
Sepp
Hallo Frank,
dein "Dauerfahrten"-Blatt sieht jetzt ja auch anders aus!
Ich hab das im Code angepasst und in meinen Augen stimmt das Ergebnis!
https://www.herber.de/bbs/user/112926.xls
Gruß Sepp
AW: neuer Code
17.04.2017 15:50:53
Frank
Hallo Sepp,
ich kann dir jetzt zwar überhaupt nicht folgen was du meinen könntest,da ich deinen ersten Code mit genau demselben Tabellenblatt "Dauerfahrten" getestet habe und da hat es einwandfrei funktioniert. Sei´s drum,jetzt funktioniert es perfekt und genauso wie ich es haben wollte. ich bin begeistert.
Vielen herzlichen Dank für deine Hilfe
Auf der Suche,wo du im Code die Schriftgröße angegeben hast,habe ich mal ein bisschen gespielt und eventuell was gelernt.
TextToDisplay:=Mid(.Cells(lngI, 2), InStr(1, .Cells(lngI, 2), Chr(10)) + 1)
objChk.Cells(lngNext, 3) = Mid(.Cells(lngI, 3), InStr(1, .Cells(lngI, 3), Chr(10)) + 1)
Ich hab zunächst vermutet,die 10 nach Chr wäre die Schriftgrösse und habe die mal auf 11 gesetzt. Im Ergebnis hatte ich auf einmal die gesamte Adresse drin und da ist mir deine erste Antwort mit der Formatierung eingefallen. Zeichen(10) besagt dort,das alles ab dem Zeilenumbruch dargestellt wird. Ich tippe jetzt mal,dass Chr(10) genau dasselbe macht,stimmts?
Die Adressen,hab ich jetzt nochmal in Dauerfahrten Spalte E gesetzt(es sind ja nur ca.20) und habe Spalte B mit der Formel gefüllt: =TEIL(E30;FINDEN(ZEICHEN(10); E30)+1;99) ff.
Eventuell überlege ich mir später nochmal was,um A,B und C aus Dauerfahrten in einem Rutsch in den Tagesbericht zu übernehmen,aber der Aufwand wird wahrscheinlich auch wider größer,da ich ja immer nur eine Tour eingebe.Das ist aber Zukunftsmusik.
Nochmals vielen vielen Dank Sepp,ich bin wirklich begeistert von dem Resultat.
Lg Frank
AW: neuer Code
17.04.2017 15:56:07
Sepp
Hallo Frank,
in der ersten Datei haben die Dauerfahrten ab Zeile 5 begonnen, in der neuen ab Zeile 2!
Gruß Sepp
AW: neuer Code
17.04.2017 18:21:22
Frank
Ach deswegen,wunderte mich nur weil der erste Code in dem jetzigen Tabellenblatt auch geht,allerdings nur der erste Fehler,mehr hatte ich ja nicht drin zum Testen.Aber jetzt sieht das richtig gut aus,einfach TOP.Danke nochmals :-)
AW: neuer Code
18.04.2017 15:05:47
Frank
Hallo Sepp,
gestern Abend ist mir im Code für das Einfärben der Zeile etwas aufgefallen.Dieser Code scheint mir irgendwie die Farbzuweisung der Zellen M10-M37 im Tabellenblatt Monat zu stören,wodurch dann der Wert in Spalte o nicht wiedergegeben wird. Wärst du so nett und schaust mal,wie man das beheben kann?
Lg Frank
AW: neuer Code
18.04.2017 20:12:35
Sepp
Hallo Frank,
klar, da du mit deiner Version in allen blättern die Zeilen färbst und dadurch die vorherige Farbe löschst. deshalb hatte ich die Version die nur auf den Tagesblättern wirksam ist.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Static AlteZelle As Range
If IsDate(Sh.Name) Then
  If Not AlteZelle Is Nothing Then
    AlteZelle.EntireRow.Interior.ColorIndex = xlColorIndexNone
  End If
  
  Target.EntireRow.Interior.ColorIndex = 15
  Set AlteZelle = Target
End If
End Sub

Gruß Sepp
AW: neuer Code
18.04.2017 22:08:18
Frank
Hallo Sepp,
stimmt so nicht ganz.Ich habe alles raus geschmissen,was ich da mal irgendwo eingebaut habe.
Dieser Code von dir ist zur Zeit aktiv:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
If IsDate(.Name) Then
.Cells.Interior.ColorIndex = xlNone
If Target.Column < 5 Then
.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 4)).Interior.ColorIndex = 15
End If
End If
End With
End Sub
Werde den neuen später mal einbauen.
Lg Frank
AW: neuer Code
19.04.2017 09:46:55
Frank
Guten Morgen Sepp,
in deinem Code sprichst du doch mit

If IsDate(.Name) Then
alle Sheets an,die ein Datum als Namen haben.Kann man das jetzt nicht noch spezifizieren,in dem man sagt,nur Sheets mit dem Datumsformat ("dd mmmm")als Namen?
So hab ich mir das in etwa gedacht

If IsDate(.Name) And
If IsDate("dd mmmm")

lass uns vorne weitermachen
19.04.2017 16:54:58
Sepp
mein Browser wird sonst zu schmal ;-))
Gruß Sepp
AW: neuer Code
18.04.2017 23:14:07
Frank
Hallo Sepp,
habe nun den neuen Code eingebaut und es ist immer noch dasselbe.Da mit dein erster Code eh besser gefällt lasse ich den drin und muss mir bezüglich der Einfärbung etwas anderes einfallen lassen.
Lg Frank
Hier gehts weiter!

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige