Show More
| @@ -1,217 +1,221 | |||
|
|
1 | 1 | Sub AktualisiereXYDiagrammWiderstand() No newline at end of file |
|
|
2 | 2 | Dim datenWs As Worksheet No newline at end of file |
|
|
3 | 3 | Dim diagrammWs As Worksheet No newline at end of file |
|
|
4 | 4 | Dim chartObj As ChartObject No newline at end of file |
|
|
5 | 5 | Dim diagramm As Chart No newline at end of file |
|
|
6 | 6 | Dim xRange As Range No newline at end of file |
|
|
7 | 7 | Dim yRange As Range No newline at end of file |
|
|
8 | 8 | Dim letzteSpalte As Long No newline at end of file |
|
|
9 | 9 | Dim letzteZeile As Long No newline at end of file |
|
|
10 | 10 | Dim i As Long No newline at end of file |
|
|
11 | 11 | ' Arbeitsblätter definieren No newline at end of file |
|
|
12 | 12 | Set datenWs = ThisWorkbook.Worksheets(1) ' Blatt mit den Daten No newline at end of file |
|
|
13 | 13 | Set diagrammWs = ThisWorkbook.Worksheets(3) ' Blatt mit dem Diagramm No newline at end of file |
|
|
14 | 14 | ' Diagramm definieren No newline at end of file |
|
|
15 | 15 | Set chartObj = diagrammWs.ChartObjects(1) ' Diagrammname anpassen No newline at end of file |
|
|
16 | 16 | Set diagramm = chartObj.Chart No newline at end of file |
|
|
17 | 17 | ' Letzte Zeile und Spalte der Daten finden No newline at end of file |
|
|
18 | 18 | letzteZeile = datenWs.Cells(datenWs.Rows.Count, 1).End(xlUp).Row ' X-Werte in Spalte A No newline at end of file |
|
|
19 | 19 | letzteSpalte = datenWs.Cells(3, datenWs.Columns.Count).End(xlToLeft).Column ' Y-Werte ab Spalte B No newline at end of file |
|
|
20 | 20 | ' MsgBox letzteZeile, vbInformation No newline at end of file |
|
|
21 | 21 | ' MsgBox letzteSpalte, vbInformation No newline at end of file |
|
|
22 | 22 | ' X-Werte-Bereich definieren (fix in Spalte A, ab Zeile 3) No newline at end of file |
|
|
23 | 23 | Set xRange = datenWs.Range(datenWs.Cells(3, 1), datenWs.Cells(letzteZeile, 1)) ' Daten beginnen ab Zeile 3 No newline at end of file |
|
|
24 | 24 | ' Bestehende Datenreihen im Diagramm entfernen No newline at end of file |
|
|
25 | 25 | Do While diagramm.SeriesCollection.Count > 0 No newline at end of file |
|
|
26 | 26 | diagramm.SeriesCollection(1).Delete No newline at end of file |
|
|
27 | 27 | Loop No newline at end of file |
|
|
28 | 28 | ' Neue Datenreihen hinzufügen No newline at end of file |
|
|
29 | 29 | For i = 2 To letzteSpalte ' Y-Werte beginnen in Spalte B No newline at end of file |
|
|
30 | 30 | Set yRange = datenWs.Range(datenWs.Cells(3, i), datenWs.Cells(letzteZeile, i)) No newline at end of file |
|
|
31 | 31 | ' Neue Datenreihe hinzufügen No newline at end of file |
|
|
32 | 32 | With diagramm.SeriesCollection.NewSeries No newline at end of file |
|
|
33 | 33 | .Name = datenWs.Cells(2, i).Value ' Name der Reihe aus der Kopfzeile (Zeile 1) No newline at end of file |
|
|
34 | 34 | .XValues = xRange No newline at end of file |
|
|
35 | 35 | .Values = yRange No newline at end of file |
|
|
36 | .Smooth = False ' Glätten ausschalten No newline at end of file | |
|
|
36 | 37 | End With No newline at end of file |
|
|
37 | 38 | Next i No newline at end of file |
|
|
38 | 39 | End Sub No newline at end of file |
|
|
39 | 40 | No newline at end of file |
|
|
40 | 41 | Sub AktualisiereXYDiagrammKraft() No newline at end of file |
|
|
41 | 42 | Dim datenWs As Worksheet No newline at end of file |
|
|
42 | 43 | Dim diagrammWs As Worksheet No newline at end of file |
|
|
43 | 44 | Dim chartObj As ChartObject No newline at end of file |
|
|
44 | 45 | Dim diagramm As Chart No newline at end of file |
|
|
45 | 46 | Dim xRange As Range No newline at end of file |
|
|
46 | 47 | Dim yRange As Range No newline at end of file |
|
|
47 | 48 | Dim letzteSpalte As Long No newline at end of file |
|
|
48 | 49 | Dim letzteZeile As Long No newline at end of file |
|
|
49 | 50 | Dim i As Long No newline at end of file |
|
|
50 | 51 | ' Arbeitsblätter definieren No newline at end of file |
|
|
51 | 52 | Set datenWs = ThisWorkbook.Worksheets(2) ' Blatt mit den Daten No newline at end of file |
|
|
52 | 53 | Set diagrammWs = ThisWorkbook.Worksheets(4) ' Blatt mit dem Diagramm No newline at end of file |
|
|
53 | 54 | ' Diagramm definieren No newline at end of file |
|
|
54 | 55 | Set chartObj = diagrammWs.ChartObjects(1) ' Diagrammname anpassen No newline at end of file |
|
|
55 | 56 | Set diagramm = chartObj.Chart No newline at end of file |
|
|
56 | 57 | ' Letzte Zeile und Spalte der Daten finden No newline at end of file |
|
|
57 | 58 | letzteZeile = datenWs.Cells(datenWs.Rows.Count, 1).End(xlUp).Row ' X-Werte in Spalte A No newline at end of file |
|
|
58 | 59 | letzteSpalte = datenWs.Cells(3, datenWs.Columns.Count).End(xlToLeft).Column ' Y-Werte ab Spalte B No newline at end of file |
|
|
59 | 60 | 'MsgBox letzteZeile, vbInformation No newline at end of file |
|
|
60 | 61 | 'MsgBox letzteSpalte, vbInformation No newline at end of file |
|
|
61 | 62 | ' X-Werte-Bereich definieren (fix in Spalte A, ab Zeile 3) No newline at end of file |
|
|
62 | 63 | Set xRange = datenWs.Range(datenWs.Cells(3, 1), datenWs.Cells(letzteZeile, 1)) ' Daten beginnen ab Zeile 3 No newline at end of file |
|
|
63 | 64 | ' Bestehende Datenreihen im Diagramm entfernen No newline at end of file |
|
|
64 | 65 | Do While diagramm.SeriesCollection.Count > 0 No newline at end of file |
|
|
65 | 66 | diagramm.SeriesCollection(1).Delete No newline at end of file |
|
|
66 | 67 | Loop No newline at end of file |
|
|
67 | 68 | ' Neue Datenreihen hinzufügen No newline at end of file |
|
|
68 | 69 | For i = 2 To letzteSpalte ' Y-Werte beginnen in Spalte B No newline at end of file |
|
|
69 | 70 | Set yRange = datenWs.Range(datenWs.Cells(3, i), datenWs.Cells(letzteZeile, i)) No newline at end of file |
|
|
70 | 71 | ' Neue Datenreihe hinzufügen No newline at end of file |
|
|
71 | 72 | With diagramm.SeriesCollection.NewSeries No newline at end of file |
|
|
72 | 73 | .Name = datenWs.Cells(2, i).Value ' Name der Reihe aus der Kopfzeile (Zeile 1) No newline at end of file |
|
|
73 | 74 | .XValues = xRange No newline at end of file |
|
|
74 | 75 | .Values = yRange No newline at end of file |
|
|
76 | .Smooth = False ' Glätten ausschalten No newline at end of file | |
|
|
75 | 77 | End With No newline at end of file |
|
|
76 | 78 | Next i No newline at end of file |
|
|
77 | 79 | End Sub No newline at end of file |
|
|
78 | 80 | No newline at end of file |
|
|
79 | 81 | Sub AktualisiereXYDiagrammInkrementWiderstand() No newline at end of file |
|
|
80 | 82 | Dim wsAktuell As Worksheet No newline at end of file |
|
|
81 | 83 | Dim ws As Worksheet No newline at end of file |
|
|
82 | 84 | Dim chartObj As ChartObject No newline at end of file |
|
|
83 | 85 | Dim diagramm As Chart No newline at end of file |
|
|
84 | 86 | Dim letzteZeile As Long No newline at end of file |
|
|
85 | 87 | Dim letzteSpalte As Long No newline at end of file |
|
|
86 | 88 | Dim datenBereichX As Range No newline at end of file |
|
|
87 | 89 | Dim datenBereichY1 As Range No newline at end of file |
|
|
88 | 90 | Dim datenReihe As Series No newline at end of file |
|
|
89 | 91 | Dim diagrammTitel As String No newline at end of file |
|
|
90 | 92 | Dim startZeile As Long No newline at end of file |
|
|
91 | 93 | Dim blockAnzahl As Long No newline at end of file |
|
|
92 | 94 | Dim blockStartSpalte As Long No newline at end of file |
|
|
93 | 95 | Dim blockEndSpalte As Long No newline at end of file |
|
|
94 | 96 | Dim blockIndex As Integer No newline at end of file |
|
|
95 | 97 | No newline at end of file |
|
|
96 | 98 | ' Arbeitsblatt "Widerstandsdiagramm" festlegen No newline at end of file |
|
|
97 | 99 | Set wsAktuell = ThisWorkbook.Worksheets("Widerstandsdiagramm") No newline at end of file |
|
|
98 | 100 | No newline at end of file |
|
|
99 | 101 | ' Diagramm auf "Widerstandsdiagramm" suchen No newline at end of file |
|
|
100 | 102 | If wsAktuell.ChartObjects.Count = 0 Then No newline at end of file |
|
|
101 | 103 | MsgBox "Kein Diagramm auf 'Widerstandsdiagramm' gefunden.", vbExclamation No newline at end of file |
|
|
102 | 104 | Exit Sub No newline at end of file |
|
|
103 | 105 | End If No newline at end of file |
|
|
104 | 106 | No newline at end of file |
|
|
105 | 107 | Set chartObj = wsAktuell.ChartObjects(1) No newline at end of file |
|
|
106 | 108 | Set diagramm = chartObj.Chart No newline at end of file |
|
|
107 | 109 | No newline at end of file |
|
|
108 | 110 | ' Vorhandene Datenreihen löschen No newline at end of file |
|
|
109 | 111 | Do While diagramm.SeriesCollection.Count > 0 No newline at end of file |
|
|
110 | 112 | diagramm.SeriesCollection(1).Delete No newline at end of file |
|
|
111 | 113 | Loop No newline at end of file |
|
|
112 | 114 | No newline at end of file |
|
|
113 | 115 | ' Alle Blätter vor "Widerstandsdiagramm" durchlaufen No newline at end of file |
|
|
114 | 116 | For Each ws In ThisWorkbook.Worksheets No newline at end of file |
|
|
115 | 117 | If ws.Index < wsAktuell.Index Then No newline at end of file |
|
|
116 | 118 | ' Letzte Zeile im Blatt ermitteln No newline at end of file |
|
|
117 | 119 | letzteZeile = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row No newline at end of file |
|
|
118 | 120 | No newline at end of file |
|
|
119 | 121 | ' Prüfen, ob relevante Daten vorhanden sind No newline at end of file |
|
|
120 | 122 | If letzteZeile >= 3 Then No newline at end of file |
|
|
121 | 123 | ' Startzeile und Anzahl der Datenblöcke definieren No newline at end of file |
|
|
122 | 124 | startZeile = 3 No newline at end of file |
|
|
123 | 125 | blockStartSpalte = 1 ' Erste Spalte für x No newline at end of file |
|
|
124 | 126 | blockEndSpalte = 3 ' Spalten x, y1, y2 No newline at end of file |
|
|
125 | 127 | blockAnzahl = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column / 3 ' Anzahl Blöcke No newline at end of file |
|
|
126 | 128 | No newline at end of file |
|
|
127 | 129 | ' Datenblöcke durchlaufen No newline at end of file |
|
|
128 | 130 | For blockIndex = 0 To blockAnzahl - 1 No newline at end of file |
|
|
129 | 131 | ' X-Werte und Y1-Werte für den aktuellen Block definieren No newline at end of file |
|
|
130 | 132 | With ws No newline at end of file |
|
|
131 | 133 | Set datenBereichX = .Range(.Cells(startZeile, blockStartSpalte + blockIndex * 3), _ No newline at end of file |
|
|
132 | 134 | .Cells(letzteZeile, blockStartSpalte + blockIndex * 3)) ' Spalte X No newline at end of file |
|
|
133 | 135 | Set datenBereichY1 = .Range(.Cells(startZeile, blockStartSpalte + blockIndex * 3 + 1), _ No newline at end of file |
|
|
134 | 136 | .Cells(letzteZeile, blockStartSpalte + blockIndex * 3 + 1)) ' Spalte Y1 No newline at end of file |
|
|
135 | 137 | No newline at end of file |
|
|
136 | 138 | ' Neue Datenreihe hinzufügen No newline at end of file |
|
|
137 | 139 | Set datenReihe = diagramm.SeriesCollection.NewSeries No newline at end of file |
|
|
138 | 140 | datenReihe.Values = datenBereichY1 No newline at end of file |
|
|
139 | 141 | datenReihe.XValues = datenBereichX No newline at end of file |
|
|
140 | 142 | datenReihe.Name = ws.Name & " - Position " & (blockIndex + 1) No newline at end of file |
|
|
143 | datenReihe.Smooth = False No newline at end of file | |
|
|
141 | 144 | End With No newline at end of file |
|
|
142 | 145 | Next blockIndex No newline at end of file |
|
|
143 | 146 | End If No newline at end of file |
|
|
144 | 147 | End If No newline at end of file |
|
|
145 | 148 | Next ws No newline at end of file |
|
|
146 | 149 | End Sub No newline at end of file |
|
|
147 | 150 | No newline at end of file |
|
|
148 | 151 | Sub AktualisiereXYDiagrammInkrementKraft() No newline at end of file |
|
|
149 | 152 | Dim wsWiderstand As Worksheet No newline at end of file |
|
|
150 | 153 | Dim wsKraft As Worksheet No newline at end of file |
|
|
151 | 154 | Dim ws As Worksheet No newline at end of file |
|
|
152 | 155 | Dim chartObj As ChartObject No newline at end of file |
|
|
153 | 156 | Dim diagramm As Chart No newline at end of file |
|
|
154 | 157 | Dim letzteZeile As Long No newline at end of file |
|
|
155 | 158 | Dim datenBereichX As Range No newline at end of file |
|
|
156 | 159 | Dim datenBereichY2 As Range No newline at end of file |
|
|
157 | 160 | Dim datenReihe As Series No newline at end of file |
|
|
158 | 161 | Dim diagrammTitel As String No newline at end of file |
|
|
159 | 162 | Dim startZeile As Long No newline at end of file |
|
|
160 | 163 | Dim blockAnzahl As Long No newline at end of file |
|
|
161 | 164 | Dim blockStartSpalte As Long No newline at end of file |
|
|
162 | 165 | Dim blockEndSpalte As Long No newline at end of file |
|
|
163 | 166 | Dim blockIndex As Integer No newline at end of file |
|
|
164 | 167 | No newline at end of file |
|
|
165 | 168 | ' Arbeitsblätter "Widerstandsdiagramm" und "Kraftdiagramm" festlegen No newline at end of file |
|
|
166 | 169 | Set wsWiderstand = ThisWorkbook.Worksheets("Widerstandsdiagramm") No newline at end of file |
|
|
167 | 170 | Set wsKraft = ThisWorkbook.Worksheets("Kraftdiagramm") No newline at end of file |
|
|
168 | 171 | No newline at end of file |
|
|
169 | 172 | ' Diagramm auf "Kraftdiagramm" suchen No newline at end of file |
|
|
170 | 173 | If wsKraft.ChartObjects.Count = 0 Then No newline at end of file |
|
|
171 | 174 | MsgBox "Kein Diagramm auf 'Kraftdiagramm' gefunden.", vbExclamation No newline at end of file |
|
|
172 | 175 | Exit Sub No newline at end of file |
|
|
173 | 176 | End If No newline at end of file |
|
|
174 | 177 | No newline at end of file |
|
|
175 | 178 | Set chartObj = wsKraft.ChartObjects(1) No newline at end of file |
|
|
176 | 179 | Set diagramm = chartObj.Chart No newline at end of file |
|
|
177 | 180 | No newline at end of file |
|
|
178 | 181 | ' Vorhandene Datenreihen löschen No newline at end of file |
|
|
179 | 182 | Do While diagramm.SeriesCollection.Count > 0 No newline at end of file |
|
|
180 | 183 | diagramm.SeriesCollection(1).Delete No newline at end of file |
|
|
181 | 184 | Loop No newline at end of file |
|
|
182 | 185 | No newline at end of file |
|
|
183 | 186 | ' Alle Blätter vor "Widerstandsdiagramm" durchlaufen No newline at end of file |
|
|
184 | 187 | For Each ws In ThisWorkbook.Worksheets No newline at end of file |
|
|
185 | 188 | If ws.Index < wsWiderstand.Index Then No newline at end of file |
|
|
186 | 189 | ' Letzte Zeile im Blatt ermitteln No newline at end of file |
|
|
187 | 190 | letzteZeile = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row No newline at end of file |
|
|
188 | 191 | No newline at end of file |
|
|
189 | 192 | ' Prüfen, ob relevante Daten vorhanden sind No newline at end of file |
|
|
190 | 193 | If letzteZeile >= 3 Then No newline at end of file |
|
|
191 | 194 | ' Startzeile und Anzahl der Datenblöcke definieren No newline at end of file |
|
|
192 | 195 | startZeile = 3 No newline at end of file |
|
|
193 | 196 | blockStartSpalte = 1 ' Erste Spalte für x No newline at end of file |
|
|
194 | 197 | blockEndSpalte = 3 ' Spalten x, y1, y2 No newline at end of file |
|
|
195 | 198 | blockAnzahl = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column / 3 ' Anzahl Blöcke No newline at end of file |
|
|
196 | 199 | No newline at end of file |
|
|
197 | 200 | ' Datenblöcke durchlaufen No newline at end of file |
|
|
198 | 201 | For blockIndex = 0 To blockAnzahl - 1 No newline at end of file |
|
|
199 | 202 | ' X-Werte und Y2-Werte für den aktuellen Block definieren No newline at end of file |
|
|
200 | 203 | With ws No newline at end of file |
|
|
201 | 204 | Set datenBereichX = .Range(.Cells(startZeile, blockStartSpalte + blockIndex * 3), _ No newline at end of file |
|
|
202 | 205 | .Cells(letzteZeile, blockStartSpalte + blockIndex * 3)) ' Spalte X No newline at end of file |
|
|
203 | 206 | Set datenBereichY2 = .Range(.Cells(startZeile, blockStartSpalte + blockIndex * 3 + 2), _ No newline at end of file |
|
|
204 | 207 | .Cells(letzteZeile, blockStartSpalte + blockIndex * 3 + 2)) ' Spalte Y2 No newline at end of file |
|
|
205 | 208 | No newline at end of file |
|
|
206 | 209 | ' Neue Datenreihe hinzufügen No newline at end of file |
|
|
207 | 210 | Set datenReihe = diagramm.SeriesCollection.NewSeries No newline at end of file |
|
|
208 | 211 | datenReihe.Values = datenBereichY2 No newline at end of file |
|
|
209 | 212 | datenReihe.XValues = datenBereichX No newline at end of file |
|
|
210 | 213 | datenReihe.Name = ws.Name & " - Position " & (blockIndex + 1) No newline at end of file |
|
|
214 | datenReihe.Smooth = False No newline at end of file | |
|
|
211 | 215 | End With No newline at end of file |
|
|
212 | 216 | Next blockIndex No newline at end of file |
|
|
213 | 217 | End If No newline at end of file |
|
|
214 | 218 | End If No newline at end of file |
|
|
215 | 219 | Next ws No newline at end of file |
|
|
216 | 220 | End Sub No newline at end of file |
|
|
217 | 221 | No newline at end of file |
|
|
1 | NO CONTENT: modified file |
General Comments 0
You need to be logged in to leave comments.
Login now