Herbers Excel-Forum - das Archiv

Sporadisch auftauchender #BEZUG!-Fehler

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 14.12.2006 09:04:19
Liebe Excel-Profis!
Ich habe ein Excel-File mit 16 Sheets, wobei alle Sheets in irgendeiner Weise auf Felder des ersten Sheets zugreifen. Das File ist eine leere Vorlage, die wöchentlich bearbeitet und dann als KW XX abgespeichert wird.
Nun das Seltsame:
Etwa alle drei Wochen sind in allen Sheets die Verweise von ein bis drei Feldern kaputt und in den Feldern steht dann #BEZUG! bzw die Formel ändert sich von:
='Arbeitsblatt1'!F6
in
='Arbeitsblatt1'!#BEZUG!
In der Vorlage ist alles in Ordnung, und meistens stimmt auch mit dem ausgefüllten File alles, aber eben nicht immer. Es sind auch immer andere Felder, und wenn man dann per Hand #BEZUG! wieder in das jeweilige Feld ändert ist alles wieder in Ordnung.
Da ich per Buttonklick alle relevanten Sheets ausdrucke(etwa 40 Seiten), ist das Kontrollieren jedes einzelnen Verweises sehr mühsam. Nun endlich meine Frage:
1.Woher kommt dieser Fehler und kann man ihn abstellen?
2. Wenn der Fehler nicht auffindbar ist, kann man bei dem 'Drucken'-Button vorher per Makro eine Abfrage starten, so etwa:
Suche in Sheets ABC bis XYZ nach "#BEZUG!"
idealerweise gleich ändern in die richtige Formel (Kann man Zellen ein feste Formel zuweisen?)
Zur Not auch in die gefundene Zelle Springen.
Ist das eine oder andere möglich?
Gruss Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Hammes
Geschrieben am: 14.12.2006 11:13:50
Hi!
wenn du das erste Sheet (auf das alle anderen Verweisen) löscht oder umbenennst, kann das bei größeren Dateien manchmal zu Streß führen.
Wie du due Bezugsfehler automatisch auslesen kannst, weiß ich hingegen nicht.
Gruß,
Bastian
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael
Geschrieben am: 14.12.2006 12:46:04
Das Sheet selbst benenne ich nicht um, nur das gesamte File. Naja, ich muss wohl hoffen, dass jemand von euch eine Idee zur Lösung dieses Problems hat.
Danke auf jeden Fall schon mal für die Antwort.
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 14.12.2006 13:42:37
Hi Michael,
Vorschlag für die Fehlersuche per VBA:
Dim c As Range
For Each c In ActiveSheet.UsedRange
If IsError(c) Then
MsgBox "Fehler gefunden"
End If
Next c
kann man natürlich noch erweitern und anpassen, aber mit IsError() findest du die fehlerhaften Zellen.
Grüße
Andreas
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 14.12.2006 14:27:04
Danke Andreas, die Formel funktioniert, nur mit dem Verfeinern tue ich mich etwas schwer. Kann ich das Ganze so verfeinern, dass Folgendes passiert:
Beim Auslösen eines Buttons, der auch die Blätter ausdruckt, soll vorher die obere Abfrage durch alle Sheets gehen und bei Fehler als msgBox anzeigen:
'Fehler in A2 im Sheet 'Sheet1'!
oder direkt dorthin wechseln.
Nachdem der Fehler behoben ist das Ganze von vorn.
Oder:
Alles durchsuchen, die gefundenen Fehler sammeln auf als msgBox anzeigen:
Gefundene Fehler:
Sheet 1 A2, B2, C2
Sheet 2 D5, D6
usw.
Oder, mein persönlicher Traum:
Da die Formeln für jede Zelle in jedem Sheet immer gleich sind, bei Finden eines Fehlers die Zelle und das Sheet checken und die dafür hinterlegte Formel einfügen.
Ist das irgendwie machbar? In meinen Augen sehr utopisch, aber ich wurde hier schon ein paarmal von genialen Ideen und Formeln überrascht.
Gruss Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 14.12.2006 14:56:32
Hi Michael,
wie wäre es denn mit dieser Lösung:
Private Sub CommandButton1_Click()
Dim c As Range
Dim i, j As Integer
Dim sh As Worksheet
Dim adresse(1000) As String
Dim arbeitsblatt(1000) As String
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.UsedRange
If IsError(c) Then
i = i + 1
adresse(i) = c.Address
arbeitsblatt(i) = c.Worksheet.Name
End If
Next c
Next sh
Sheets.Add
ActiveSheet.Cells(1, 1) = "Tabellenblatt"
ActiveSheet.Cells(1, 2) = "Adresse des Fehlers"
For j = 1 To i
ActiveSheet.Cells(j + 1, 1) = arbeitsblatt(j)
ActiveSheet.Cells(j + 1, 2) = adresse(j)
Next
ActiveSheet.Columns("A:B").EntireColumn.AutoFit
End Sub

Das mit dem Formel ersetzen ginge natürlich auch, ist aber gerade eine Zeitfrage ;-)
Grüße
Andreas
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 15.12.2006 07:38:19
Danke schon mal vielmals für die tolle Formel, funktioniert super.
Ich habe jetzt mal mit ein paar IF-Anweisungen gespielt und folgendes probiert:
Statt dem Teil, der die neue Tabelle erzeugt, habe ich:
If arbeitsblatt(i) = "Sheet 2" then
if adresse(i) = "$A$1" then Sheets("Sheet 2").Range("A1").value = "='Sheet 1'!b5" else
if adresse(i) = "$c$1" then Sheets("Sheet 2").Range("c1").value = "='Sheet 1'!d5" else
usw.
Wenn ich nur mit dem ersten adresse(i)-IF arbeite funktioniert es. Sobald ich aber wie oben mehrere Zeilen schreibe tauscht er gar nichts mehr aus.
Muss ich erst zurück nach oben mit next c oder ähnlichem?
Danke schon mal und Gruss
Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 15.12.2006 09:33:19
Ich habe jetzt noch etwas gebastelt, hänge aber noch an der Umsetzung:
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.UsedRange
If IsError(c) Then
i = i + 1
adresse(i) = c.Address
arbeitsblatt(i) = c.Worksheet.Name
If arbeitsblatt(i) = "Sheet 2" Then
Sheets("sheet 2").Range(adresse(i)).Value = "='Sheet 1'!" & ????
So, hier haperts an der Range. In Sheet 2 sind die Felder a1, c1, e1, g1 und i1 relevant, verformelt mit b5, d5, f5, h5 und j5. Ich will ihm jetzt sagen:
Wenn c.Address = a1 dann ??? = b5, wenn c1 dann d5 usw.
Wie kann ich die Ranges aufeinander beziehen?
Dann besteht noch das Problem, ob der IF Ansatz überhaupt richtig ist, denn ich muss ja dann mit Sheet 3 weitermachen, wo die Bezüge anders sind, also:
....
If arbeitsblatt(i) = "Sheet 2" Then
Sheets("sheet 2").Range(adresse(i)).Value = "='Sheet 1'!" & ????
else if arbeitsblatt(i) = "Sheet 3" Then
Sheets("sheet 3").Range(adresse(i)).Value = "='Sheet 1'!" & 2?2?
Und da wäre dann der Bezug zu Sheet 1 leicht verändert.
Ich habe mir überlegt, ob es funktionieren würde, wenn man etwa so vorgeht:
Dim strsheet2 as String
Dim strsheet3 as String
usw...
Dim r1 as range
in der For Next Schleife:
r1 = c.Address
und irgendwo:
strsheet2 = WorksheetFunction.Choose(R1, "b5", "d5" usw... )
strsheet3 = WorksheetFunction usw...
Und dann bei der IF-Funktion:
If arbeitsblatt(i) = "Sheet 2" Then
Sheets("sheet 2").Range(adresse(i)).Value = "='Sheet 1'!" & strsheet2
Leider weiss ich nicht genau, wie das alles funktioniert, da ich Quereinsteiger bin und meist durch Rumprobieren oder Rumfragen auf funktionierende Makros komme. Ist das irgendwie umsetzbar oder totaler Blödsinn?
Gruss Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 15.12.2006 11:23:23
Hi Michael,
so ganz verstanden habe ich das noch nicht. So wie ich es verstehe ist in jedem Blatt zu prüfen, ob in den Zellen A1, C1, E1, G1 und I1 zu prüfen, ob ein Fehler besteht. Wenn ja, dann sind die dort stehenden Formel zu ersetzen mit =Sheet1!B5, =Sheet1!D5, =Sheet1!F5, =Sheet1!H5 und =Sheet1!J5.
Insofern verstehe ich das Problem noch nicht ganz, weil du doch nur selektieren musst, ob die gefundene Adresse eine der zu prüfenden ist, und dann ersetzt du eben in Abhängigkeit der gefundenen Adresse, welche der fünf Ersatzmöglichkeiten einzusetzen ist.
Oder ist das doch alles viel komplizierter?
Vielleicht würde ich eine Beispieldatei weiter helfen ...
Ich lasse den Post mal offen, da ich auch noch nicht weiß, ob ich ihn abschließend beantworten kann.
Grüße
Andreas
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 15.12.2006 13:08:11
Beispielfile:
https://www.herber.de/bbs/user/39026.xls
Es ist leider nicht ganz so einfach. Im Beispielfile:
Die Einträge aus 'Speisenplan A 4' gehen in die anderen Sheets. Dummerweise sind die Bezüge nicht immer identisch.
Mit:
Dim c As Range
Dim i As Integer
Dim sh As Worksheet
Dim adresse(1000) As String
Dim arbeitsblatt(1000) As String
Dim strsheet2 As String
Dim strsheet3 As String
Dim z As Long
z = 0
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.UsedRange
If IsError(c) Then
i = i + 1
z = z + 1
adresse(i) = c.Address
arbeitsblatt(i) = c.Worksheet.Name
strsheet2 = WorksheetFunction.Choose(z, "b5", "d5", "f5", "h5", "i5")
If arbeitsblatt(i) = "Hauptgericht 1" Then
Sheets("Hauptgericht 1").Range(adresse(i)).Value = "='Speisenplan DIN A 4'!" & strsheet2
End If
End If
Next c
Next sh
klappt das Ersetzen, aber nur, wenn alle 5 Formeln defekt sind. Wenn es irgendwie möglich wäre, die Range c.Address in eine Zahl zu verwandeln sind wir glaube ich auf einem guten Weg. Wie gesagt, nur Vermutungen...
Gruss Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 15.12.2006 13:34:46
Hi Michael,
so wie ich das jetzt verstehe, geht es ja primär um die Blätter "Hauptgericht1" bis "Vegetarisch". Vorschlag wäre dann, dass man die Formel abhängig vom Tabellenblatt (das ja für die Zeilenzahl im Bezug entscheidend ist) und der gefundenen Adresse des Fehlers (geht ja immer nur um Zeile 1, oder?) definiert.
Ergo, wäre mein Vorschlag für die Umsetzung (allerdings ungetestet!):
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.UsedRange
If IsError(c) Then
Select Case c.Worksheet.Name
Case "Suppe"
Zeilenindex = 1
Case "Vegetarisch"
Zeilenindex = 2
Case "Hauptgericht1"
Zeilenindex = 3
Case "Menü"
Zeilenindex = 4
End Select
Select Case Left(c.Address, 2)
Case "$A"
Spaltenindex = "B"
Case "$B"
Spaltenindex = "C"
Case "$C"
Spaltenindex = "D"
' etc. etc. etc.
End Select
With Worksheets(c.Worksheet.Name)
Range(c.Address).Value = "='Speisenplan DIN A 4'!" & Spaltenindex & 2 + Zeilenindex
End With
End If
Next c
Next sh
Ich hoffe das hilft weiter.
Grüße
Andreas
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 15.12.2006 16:03:12
Danke für das Super-File, war fast perfekt bis auf die genaue Zuweisung
Sheets(c.Worksheet.Name)
vor der Range, sonst bleibt er in der Speisekarte. Da ich auch die anderen veformelten Sheets brauche habe ich mich mal am Rest versucht. Die CASE-Formeln für das Sheet 'rückstellprobe' haben sofort funktioniert, nur die Erweiterung auf die Temperaturlisten jeden Tages funktionieren nicht richtig. Es wird nur die erste Zeile richtig korrigiert, ansonsten kommt:
Laufzeitfehler '1004'
Anwendungs- oder objektdefinierter Fehler
mit Verweis auf diese Zeile:
Sheets(c.Worksheet.Name).Range(c.Address).Value = "='Speisenplan DIN A 4'!" & spaltenindex & 2 + Zeilenindex
Hier mal das ganze Gebilde, ist da was zu erkennen?
Dim c As Range
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.UsedRange
If IsError(c) Then
Select Case c.Worksheet.Name
Case "Suppe"
Zeilenindex = 1
Case "Vegetarisch"
Zeilenindex = 2
Case "Hauptgericht 1"
Zeilenindex = 3
Case "Menü"
Zeilenindex = 4
Case "rückstellprobe"
Select Case Right(c.Address, 1)
Case "2"
Zeilenindex = 4
Case "3"
Zeilenindex = 3
Case "4"
Zeilenindex = 1
Case "5"
Zeilenindex = 2
Case "9"
Zeilenindex = 6
Case "10"
Zeilenindex = 7
End Select
End Select
Select Case Left(c.Address, 2)
Case "$A"
spaltenindex = "B"
Case "$C"
Select Case Right(c.Address, 1)
Case "1"
spaltenindex = "D"
Case "2"
spaltenindex = "D"
Case "6"
Select Case c.Worksheet.Name
Case "Temp.Montag"
spaltenindex = "B"
Case "Temp.Dienstag"
spaltenindex = "D"
Case "Temp.Mittwoch"
spaltenindex = "F"
Case "Temp.Donnerstag"
spaltenindex = "H"
Case "Temp.Freitag"
spaltenindex = "J"
End Select
Zeilenindex = 1
Case "7"
Zeilenindex = 2
Case "8"
Zeilenindex = 3
Case "9"
Zeilenindex = 4
Case "10"
Zeilenindex = 6
Case "11"
Zeilenindex = 7
End Select
Case "$E"
spaltenindex = "F"
Case "$G"
spaltenindex = "H"
Case "$I"
spaltenindex = "J"
End Select
With Worksheets(c.Worksheet.Name)
Sheets(c.Worksheet.Name).Range(c.Address).Value = "='Speisenplan DIN A 4'!" & spaltenindex & 2 + Zeilenindex
End With
End If
Next c
Next sh
PS.:
Ich kann diese Seite erst wieder am Montag abrufen, also eilt nicht so.
Auf jeden Fall scon mal 1000 Dank für die tolle Unterstützung!!!
Michael
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 18.12.2006 13:11:02
Hallo,
hatte leider bislang keine Zeit mir das anzusehen und es sieht auch für heute und morgen schlecht aus. Wenn der Fehler kommt und du auf Debuggen klickst, dann ist die Zeile ja gelb markiert, evtl. mal mit der Maus drüber fahren und die Variablenwerte prüfen, an welcher Stelle er genau hängen bleibt und dann davon ausgehend den Fehler suchen. Vielleicht stellst du auch die Datei mal online, damit man den Fehler live sehen kann.
Grüße
Andreas
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Michael

Geschrieben am: 19.12.2006 13:11:04
Geschafft!
Da ich die Temperaturlisten wenn dann nur täglich ausdrucke habe ich die einzelnen IsError und CASE-SELECT Anweisungen jetzt mit dem Sheet direkt verknüpft, dann funtioniert es einwandfrei. In der allgemeinen Abfrage c.Worksheet.Name habe ich bei den Temperatur-Sheets ein
goto Abfrage
eingebaut und
Abfrage:
hinter die With und vor die Next-Anweisungen gehängt, damit der #Bezug-Fehler in diesen Sheets stehen bleibt, damit IsError später auch noch was zum Ersetzen findet.
Vielen Dank für die tollen Anregungen, hat mir extrem geholfen!
Bild

Betrifft: AW: Sporadisch auftauchender #BEZUG!-Fehler
von: Andreas Emmert

Geschrieben am: 20.12.2006 08:49:39
Hallo Michael,
freut mich, dass ich helfen konnte und danke für die Rückmeldung!
Grüße
Andreas
 Bild
Excel-Beispiele zum Thema "Sporadisch auftauchender #BEZUG!-Fehler"
Alle Zellen mit der #BEZUG!-Fehlermeldung auswählen