Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA: Zellen formatieren über mehrere Tabellen

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

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

Betreff
Datum
Anwender
Anzeige
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

Anzeige
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
Anzeige
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
Anzeige
AW: VBA: Zellen formatieren über mehrere Tabellen
05.10.2017 09:57:10
Christian
Hallo Bernd,
diese Variante erledigt den Job deutlich ef­fi­zi­enter...
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

Anzeige
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

323 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige