ich brauche dringend Hilfe...
Ich habe ein Makro geschreiben zur Auswertung von Daten, die in einem Diagramm dargestellt werden sollen.
Die Anzahl der Daten der einzelnen Kennlinien ist allerdings dynamisch, nach jeder Messung habe ich unterschiedlich viele Daten.
Insgesamt sind es 6 Datenreihen die ich richtig ornen uss und dann in Tabelle übertragen.
Dazu habe ich folgendes Makro zusammen geschustert, allerdings läuft es mehr schlecht als Recht.
Hat nicht noch jemand Tips?
Vielleicht kommt ja auch wer aus Braunschweig und Umgebung, der mir hier weiterhelfen könnte!
Es ist wirklich dringend!
Vielen Dank.
Sub Tabelle_einzeln()
Dim Bezeichnung1 As Variant
Dim Anfang1 As Variant
Dim AnfangWiderstand As Variant
Dim Zahlenanfang As Variant
Dim Zahlenanfang1 As Variant
Dim Temperatur As Object
Dim Widerstandswerte As Variant
Dim Widerstandswerte1 As Variant
Dim Bezeichnung_Kennlinie As Variant
Dim Zahlenreihe1x As String
Dim Zahlenreihe1y As String
Dim Zahlenreihe2x As String
Dim Zahlenreihe2y As String
Dim Zahlenreihe3x As String
Dim Zahlenreihe3y As String
Dim Zahlenreihe4x As String
Dim Zahlenreihe4y As String
Dim Zahlenreihe5x As String
Dim Zahlenreihe5y As String
Dim Zahlenreihe6x As String
Dim Zahlenreihe6y As String
ChDir "\Dokumente und Einstellungen\Privat.COMPUTERNAME\Desktop\MEF"
ChDrive "c:\"
'Das Dialogfenster
Bezeichnung1 = Application.GetOpenFilename("Alle Dateien (*.*), *.*")
If Bezeichnung1 = False Then Exit Sub
Sheets("Tabelle1").Select
Workbooks.OpenText Filename:=Bezeichnung1 _
, Origin:=932, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=False
Bezeichnung_Kennlinie = ActiveCell.Worksheet.Name
Set Fundstelle = Range("A4:A25").Find("Kraft[N]")
If Not Fundstelle Is Nothing Then
Fundstelle.Activate
Anfang1 = Fundstelle.Address 'Zelle für Kraft[N]
Else
MsgBox "Der Anfangswert konnte nicht ermittelt werden. Makro wird geschlossen!", vbCritical
Exit Sub
End If
Set Zahlenanfang = ActiveSheet.Range(Anfang1).Rows(2) 'erster Wert ausgewählt
Zahlenanfang.Activate
Zahlenanfang1 = Zahlenanfang.Address 'Messwertreihen ordnen
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe1x = Selection.Address
Set Widerstandswerte = ActiveSheet.Range(Zahlenanfang1).Columns(2) 'Widerstandswerte normalisieren
Widerstandswerte.Select
Range(Widerstandswerte, [B2000]).Select
Selection.NumberFormat = "General"
Widerstandswerte.Activate
Widerstandswerte1 = Widerstandswerte.Address
Widerstandswerte.Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe1y = Selection.Address
Zahlenanfang.Activate 'Rahmen setzen
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveSheet.Range(Anfang1).Columns(4).Select
Zahlenanfang.Activate 'Messwertreihen ordnen
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Zahlenanfang.Select
ActiveSheet.Range(Zahlenanfang1).Columns(4).Select
ActiveSheet.Paste
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(4).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe2x = Selection.Address
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(5).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe2y = Selection.Address
Zahlenanfang.Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveSheet.Range(Zahlenanfang1).Columns(7).Select
ActiveSheet.Paste
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(7).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe3x = Selection.Address
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(8).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe3y = Selection.Address
Zahlenanfang.Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveSheet.Range(Zahlenanfang1).Columns(10).Select
ActiveSheet.Paste
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(10).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe4x = Selection.Address
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(11).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe4y = Selection.Address
Zahlenanfang.Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Cut
ActiveSheet.Range(Zahlenanfang1).Columns(13).Select
ActiveSheet.Paste
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(13).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe5x = Selection.Address
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(14).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe5y = Selection.Address
Zahlenanfang.Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveSheet.Range(Zahlenanfang1).Columns(16).Select
ActiveSheet.Paste
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(16).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe6x = Selection.Address
Zahlenanfang.Activate
ActiveSheet.Range(Zahlenanfang1).Columns(17).Select
Range(Selection, Selection.End(xlDown)).Select
Zahlenreihe6y = Selection.Address
Zahlenanfang.Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Worksheets(Bezeichnung_Kennlinie).Range("A10:M202"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete 'vorhandene Kennlinie löschen
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries 'neue Kennlinie einfügen
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'Werte in Diagramm einfügen
ActiveChart.SeriesCollection(1).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe1x)
ActiveChart.SeriesCollection(1).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe1y)
ActiveChart.SeriesCollection(1).Name = "=""1. Kraft steigend"""
ActiveChart.SeriesCollection(2).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe2x)
ActiveChart.SeriesCollection(2).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe2y)
ActiveChart.SeriesCollection(2).Name = "=""1. Kraft fallend"""
ActiveChart.SeriesCollection(3).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe3x)
ActiveChart.SeriesCollection(3).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe3y)
ActiveChart.SeriesCollection(3).Name = "=""2. Kraft steigend"""
ActiveChart.SeriesCollection(4).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe4x)
ActiveChart.SeriesCollection(4).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe4y)
ActiveChart.SeriesCollection(4).Name = "=""2. Kraft fallend"""
ActiveChart.SeriesCollection(5).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe5x)
ActiveChart.SeriesCollection(5).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe5y)
ActiveChart.SeriesCollection(5).Name = "=""3. Kraft steigend"""
ActiveChart.SeriesCollection(6).XValues = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe6x)
ActiveChart.SeriesCollection(6).Values = Worksheets(Bezeichnung_Kennlinie).Range(Zahlenreihe6y)
ActiveChart.SeriesCollection(6).Name = "=""3. Kraft fallend"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Kennlinie"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Widerstand-Kraft-Kennlinie " & Bezeichnung_Kennlinie
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Kraft [N]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Widerstand [MOhm]"
End With