Sporadisch auftauchender #BEZUG!-Fehler

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

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

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

Beliebteste Forumthreads (12 Monate)

Sporadisch auftauchender #BEZUG!-Fehler

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Sporadisch auftauchender #BEZUG!-Fehler

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread