ich hatte einen funktionierenen Code (Bsp1) und habe eigtl meiner Meinung nach nur weitere Suchkriterien hinzugefügt (Bsp2) und bekomme jetzt den Fehler:
Laufzeitfehler 1004: Anwendung- und Objektorientierter Fehler
Bsp1:
Sub Spannungen_WAHR_FALSCH_neu()
Dim lngRow&
Dim i
Application.ScreenUpdating = False
For i = 2 To ThisWorkbook.Sheets.Count 'Für alle Tabellenblätter
Worksheets(i).Activate 'Tabellenblatt i ansprechen
Range("D1").Select
ActiveCell.FormulaR1C1 = "24V"
Range("E1").Select
ActiveCell.FormulaR1C1 = "230V" '24V und 230V als String in bestimmte Zellen eintragen
lngRow = Cells(Worksheets(i).Rows.Count, 1).End(xlUp).Row
If lngRow = 1 Then GoTo Schleifenende
'24V
Worksheets(i).Range("D2").Resize(lngRow - 1).FormulaR1C1 = _
"=OR((IF(ISNUMBER(SEARCH(""XBU6"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU6"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X24"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X24"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X3"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X3"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS24"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS24"",RC2)),""wahr"",""falsch"")))"
'230V
Worksheets(i).Range("E2").Resize(lngRow - 1).FormulaR1C1 = _
"=OR((IF(ISNUMBER(SEARCH(""XS2"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS2"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU2"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU2"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU1" & _
"1"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU1"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X8"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X8"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XXBU2"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XXBU2"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XXBU3"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XXBU3"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC2)),""wahr"",""falsch"")))" & _
""
'Wahr Falsch für Spannungen setzen in ganze Spalte solange wie in Spalte 1 Werte stehen
Schleifenende:
Next i
Application.ScreenUpdating = True
End Sub
Bsp2:
Sub Spannungen_WAHR_FALSCH_neu()
Dim lngRow&
Dim i
Application.ScreenUpdating = False
For i = 2 To ThisWorkbook.Sheets.Count 'Für alle Tabellenblätter
Worksheets(i).Activate 'Tabellenblatt i ansprechen
Range("D1").Select
ActiveCell.FormulaR1C1 = "24V"
Range("E1").Select
ActiveCell.FormulaR1C1 = "230V" '24V und 230V als String in bestimmte Zellen eintragen
lngRow = Cells(Worksheets(i).Rows.Count, 1).End(xlUp).Row
If lngRow = 1 Then GoTo Schleifenende
'24V
Worksheets(i).Range("D2").Resize(lngRow - 1).FormulaR1C1 = _
"=OR((IF(ISNUMBER(SEARCH(""X-BU6"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU6"",RC2)),""wahr"",""falsch"")), (IF(ISNUMBER(SEARCH(""X.24"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X.24"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-3"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-3"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU2.24"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU2.24"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU3"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU3"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU4"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU4" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.01"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.01"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.02"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.02"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.01"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.01"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.02"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.02"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.21"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.21"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.22"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.22" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.21"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.21"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.22"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.22"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.41"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.41"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.42"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.42"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.43"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.43"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.44"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.44" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.45"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.45"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.41"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.41"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.42"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.42"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.43"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.43"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.44"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.44"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.45"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.45" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.51"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.51"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.52"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.52"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.53"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.53"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.54"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.54"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.51"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.51"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.52"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.52" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.53"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.53"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.54"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.54"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.61"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.61"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.62"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.62"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.61"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.61"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.62"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.62"",RC2)),""wahr"",""falsch"")))"
'230V
Worksheets(i).Range("E2").Resize(lngRow - 1).FormulaR1C1 = _
"=OR((IF(ISNUMBER(SEARCH(""X-BU2.230"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-BU2.230"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XS230"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU1" & _
",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""XBU1"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2GL"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2GL"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2L"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""X-2L"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.31"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.31"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.33"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.33"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.34"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.34" & _
",RC2)),""wahr"",""falsch"")),""wahr"",""falsch""),(IF(ISNUMBER(SEARCH(""B1.32"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.32"",RC2)),""wahr"",""falsch""),(IF(ISNUMBER(SEARCH(""B1.35"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B1.35"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.31"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.31"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.32"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.32"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.33"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.33"",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.34"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.34" & _
",RC2)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.35"",RC1)),""wahr"",""falsch"")),(IF(ISNUMBER(SEARCH(""B2.35"",RC2)),""wahr"",""falsch"")))"
'Wahr Falsch für Spannungen setzen in ganze Spalte solange wie in Spalte 1 Werte stehen
Schleifenende:
Next i
Application.ScreenUpdating = True
End Sub
Ich weiß es ist viel Text aber vllt schaut ja mal jmd anderes drüber und sieht den Fehler.
Danke im Vorraus ich finde es einfach nicht.
LG