Laufzeitfehler 1004
21.04.2021 11:47:52
Jaymerson
Ich brauche mal wieder eure Hilfe!
Ich habe da ein Makro das führt zu dem Laufzeitfehler 1004 (Anwendungs- oder objektdefinierter Fehler).
Leider bin ich mit meinem Latein am ende und finde den Fehler nicht.
Kann mir jemand helfen?
Besten dank schon mal vorab
https://www.herber.de/bbs/user/145688.xlsx
Option Explicit
Public Sub SchöneMachen()
' * * * Erstmal alles hübsch machen * * *
Dim WsTab As Worksheet
For Each WsTab In Sheets
WsTab.Activate
Application.ScreenUpdating = False
'Entferne bei jeder Tabelle die ersten Zeilen bis zu "export"
'da es mal auf englisch, mal auf deutsch dort steht habe ich nur den identischen teil für die suche genutzt
Range("A1").Select
Cells.Find(What:="export", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
'entferne die Fragennummer
Range("B1").FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-SEARCH(""_"",RC[-1]))"
Range("B1").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").ClearContents
'Formatiere a1 mit Schriftgröße, Ausrichtung
Range("A1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Helvetica"
.Size = 12
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
'Formatiere a2 mit Schriftgröße, Ausrichtung, Hintergrundfarbe
Range("A2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.Bold = True
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
'Zeilenhöhe anpassen (für A1 und A2 separat)
Rows.EntireRow.AutoFit
Rows("1:1").RowHeight = 30
Rows("2:2").RowHeight = 15
Columns("a:a").ColumnWidth = 150
Columns("a:a").WrapText = True
Columns("b:f").ColumnWidth = 50
Columns("b:f").WrapText = True
'Entferne alle Rahmen
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'Entferne bei jeder Tabelle die Zeile 3
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
'A1 und B1 Zellen verbinden
If Range("b3") = "" Then
Range("A1").Select
Else
Range("A3").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(-2, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A3").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End If
'Rahmen neu setzen
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With
'Entferne überflüssige Informationen aus Überschrift
Cells.Replace What:=" (Offene Frage)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=" (Open End)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=" (Single Choice)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=" (Mehrfachantwort)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=" (Einfachantwort)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'entferne doppelte Leerzeichen
Cells.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
Next WsTab
Application.ScreenUpdating = True
' * * * Routine für Hintergrundfarbe A1 * * *
Worksheets(1).Activate
UserForm1.Show
Worksheets(2).Activate
For Each WsTab In Sheets
WsTab.Activate
'Hintergrundfarbe von A1 auf erster Tabelle setzen
Range("a1").Interior.ColorIndex = _
Worksheets(1).Range("a1").Interior.ColorIndex
Next WsTab
Worksheets(1).Activate
End Sub