VBA: Zellen formatieren über mehrere Tabellen

Forumthread: VBA: Zellen formatieren über mehrere Tabellen

04.10.2017 11:25:42

Bernd

Hallo zusammen,
Folgendes Problem und zwar habe ich 140 Tabellenblätter, die alle in Spalte B ein Datum haben (z.B. 2014-10-06) und in den Spalten C-H sind Zahlen vorhanden ( z.B. 123.654321). Diese Tabellennamen stehen in Tabelle "Kürzel" in Spalte B. Zwei Makros, die die Zahlen und das Datum formatieren haben ich schon, aber ich bekomme es nicht hin, jede Tabelle über eine Schleife anzusprechen.
Kann mir jemand helfen?
Danke im Voraus!!!
Gruß aus Bremen
Bernd
https://www.herber.de/bbs/user/116745.xlsm

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA: Zellen formatieren über mehrere Tabellen

04.10.2017 13:51:42

Christian

Hi,
ich würde es z.b. so machen:


Sub Schleife()
With ThisWorkbook.Worksheets("Kürzel")
Dim rng As Range
Set rng = .Cells(1, 1).CurrentRegion
Dim Kuerzel As Variant
For Each Kuerzel In rng.Offset(1).Resize(rng.Rows.Count - 1).Rows
With ThisWorkbook.Worksheets(Kuerzel.Cells(1, 2).Value)
' Lezte Zeile
Dim lastRow  As Long
lastRow = .Cells(1, 1).End(xlDown).Row
Dim rngCell As Variant
' Datumstext in Date umwandeln
For Each rngCell In .Range(.Cells(2, 2), .Cells(lastRow, 2))
rngCell.Value = CDate(rngCell)
Next rngCell
' Datum formatieren
With .Range(.Cells(2, 2), .Cells(lastRow, 2))
.Interior.Color = rgbLightBlue
.NumberFormat = "DD.MM.YYYY"
End With
' Text in Zahl umwandeln
For Each rngCell In .Range(.Cells(2, 2), .Cells(lastRow, 8))
If IsNumeric(rngCell) Then
rngCell.Value = rngCell.Value * 1
End If
If rngCell.Value = "null" Then
rngCell.Value = 0
End If
Next rngCell
' Zahlen formatieren
With .Range(.Cells(2, 3), .Cells(lastRow, 8))
.NumberFormat = "#,##0.00"
.Interior.Color = rgbLavender
End With
' Nachricht ausgeben
Debug.Print "Blatt " & .Name & " bearbeitet."
End With
Next Kuerzel
End With
End Sub

AW: VBA: Zellen formatieren über mehrere Tabellen

04.10.2017 14:42:17

Bernd

Hallo Christian,
Danke für deine schnelle Antwort.
Leider wird das Zahlenformat falsch dargestellt. Es handelt sich um 1 bis 3 Stellen vor dem _ Komma und 6 Stellen nach dem Komma. Wenn die Makro durchgelaufen ist, habe folgenden Wert


126.400.002,00

Aussehen sollte es so


126,400002

also Dezimalzahl mit 6 Nachkommastellen.
Kannst du mir das im Makro noch beheben? Danke im Voraus!!!
Gruß Bernd

AW: VBA: Zellen formatieren über mehrere Tabellen

04.10.2017 15:58:19

Christian

Hallo Bernd,
versuch es mal damit.....

Sub Schleife2()
With ThisWorkbook.Worksheets("Kürzel")
Dim rng As Range
Set rng = .Cells(1, 1).CurrentRegion
Dim Kuerzel As Variant
For Each Kuerzel In rng.Offset(1).Resize(rng.Rows.Count - 1).Rows
With ThisWorkbook.Worksheets(Kuerzel.Cells(1, 2).Value)
' Lezte Zeile
Dim lastRow  As Long
lastRow = .Cells(1, 1).End(xlDown).Row
Dim rngCell As Variant
' Datumstext in Date umwandeln
For Each rngCell In .Range(.Cells(2, 2), .Cells(lastRow, 2))
rngCell.Value = CDate(rngCell)
Next rngCell
' Datum formatieren
With .Range(.Cells(2, 2), .Cells(lastRow, 2))
.Interior.Color = rgbLightBlue
.NumberFormat = "DD.MM.YYYY"
End With
' Text in Zahl umwandeln
For Each rngCell In .Range(.Cells(2, 2), .Cells(lastRow, 8))
If IsNumeric(rngCell) Then
rngCell.Value = Replace(rngCell.Value, ".", ",") * 1
End If
If rngCell.Value = "null" Then
rngCell.Value = 0
End If
Next rngCell
' Zahlen formatieren
With .Range(.Cells(2, 3), .Cells(lastRow, 8))
.NumberFormat = "#,######0.000000"
.Interior.Color = rgbLavender
End With
' Zahlen formatieren
With .Range(.Cells(2, 8), .Cells(lastRow, 8))
.NumberFormat = "#,##0.00"
.Interior.Color = rgbLavender
End With
' Nachricht ausgeben
Debug.Print "Blatt " & .Name & " bearbeitet."
End With
Next Kuerzel
End With
End Sub

Achtung, es wird der Dezimaltrenner getauscht von '.' > ','
Gruß, Christian

AW: VBA: Zellen formatieren über mehrere Tabellen

04.10.2017 16:12:55

Bernd

Hallo Christian,
Du bist mein Held. Es funktioniert!!!
Danke für deine Bemühungen :-)
Lieben Gruß aus Bremen
Bernd

AW: VBA: Zellen formatieren über mehrere Tabellen

05.10.2017 00:32:37

Bernd

Hallo Christian,
zu früh gefreut, Habe bei allen 140 Tabellen versucht die Daten zu ändern und Excel stürzt ab!
Habe noch mal eine Testdatei mit 30 Tabellen hochgeladen zum testen.
Irgendetwas ist nicht ganz korrekt.
Kannst du den Fehler finden?
Grus aus Bremen
Bernd
https://www.herber.de/bbs/user/116757.xlsm

AW: VBA: Zellen formatieren über mehrere Tabellen

05.10.2017 09:19:47

Christian

Hi,
gibt es eine Fehlermeldung oder kommt Excel nicht wieder?
Gruß,
Christian

AW: VBA: Zellen formatieren über mehrere Tabellen

05.10.2017 09:57:10

Christian

Hallo Bernd,
diese Variante erledigt den Job deutlich effizienter...
Bei 140 Blätter ist das in der Tat ein Thema!

Sub Schleife3()
With ThisWorkbook.Worksheets("Kürzel")
Dim rng As Range
Set rng = .Cells(1, 1).CurrentRegion
Dim Kuerzel As Variant
' Schleife über Blätter
For Each Kuerzel In rng.Offset(1).Resize(rng.Rows.Count - 1).Rows
With ThisWorkbook.Worksheets(Kuerzel.Cells(1, 2).Value)
' Lezte Zeile
Dim lastRow  As Long
lastRow = .Cells(1, 1).End(xlDown).Row
' Bereich in Array übergeben
Dim x As Variant
x = .Range(.Cells(2, 2), .Cells(lastRow, 2))
' String in Date umwandeln
Dim i As Long
For i = LBound(x) To UBound(x)
x(i, 1) = CDate(x(i, 1))
Next i
' Array zurückschreiben
.Range(.Cells(2, 2), .Cells(lastRow, 2)) = x
' Datum formatieren
With .Range(.Cells(2, 2), .Cells(lastRow, 2))
.Interior.Color = rgbLightBlue
.NumberFormat = "DD.MM.YYYY"
End With
' Text in Zahl umwandeln
' Bereich in Array übergeben
x = .Range(.Cells(2, 2), .Cells(lastRow, 8))
For i = LBound(x) To UBound(x)
If IsNumeric(x(i, 1)) Then
x(i, 1) = Replace(x(i, 1), ".", ",") * 1
ElseIf x(i, 1) = "null" Then
x(i, 1) = 0
End If
Next i
' Array zurückschreiben
.Range(.Cells(2, 2), .Cells(lastRow, 8)) = x
' Zahlen formatieren
With .Range(.Cells(2, 3), .Cells(lastRow, 8))
.NumberFormat = "#,######0.000000"
.Interior.Color = rgbLavender
End With
' Zahlen formatieren
With .Range(.Cells(2, 8), .Cells(lastRow, 8))
.NumberFormat = "#,##0.00"
.Interior.Color = rgbLavender
End With
' Nachricht ausgeben
'Debug.Print "Blatt " & .Name & " bearbeitet."
End With
Next Kuerzel
End With
End Sub

VG, Christian

AW: VBA: Zellen formatieren über mehrere Tabellen

05.10.2017 11:12:08

Bernd

Hallo Christian,
Jetzt läuft das Makro meiner Meinung nach perfekt.
Danke, Danke, Danke, du hast mich von einer riesen Last befreit!
Bitte bleibe mir wohlgesonnen :-)
Lieben Gruß aus Bremen
Bernd

Beliebteste Forumthreads (12 Monate)

VBA: Zellen formatieren über mehrere Tabellen

Forumthread: VBA: Zellen formatieren über mehrere Tabellen

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

VBA: Zellen formatieren über mehrere Tabellen

Forumthread: VBA: Zellen formatieren über mehrere Tabellen

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread