#excel #vba
Вопрос:
У меня есть десять страниц различных данных из моего исследования. После вычислений, выполненных в начале макроданных, которые используются для графика, всегда находятся в столбцах C и D. Мне нужно нарисовать из него график, но на графике мне нужна только полимониальная линия тренда 3-й степени и ничего больше. Когда я пытаюсь это сделать и изменить лист, чтобы запустить макрос для другого образца, он не работает, и у меня есть только график без линии тренда, а также он рисует его для предыдущей страницы и не изменяет ячейки, которые находятся на новом листе. Если бы вы могли мне помочь, я был бы вам очень благодарен.
Sub Makro2()
'
'
ActiveCell.FormulaR1C1 = "sigma"
Range("D1").Select
ActiveCell.FormulaR1C1 = "epsilon"
Range("E1:G1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Wymiary [mm]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "l0"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Normalny"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Arial"
.FontStyle = "Normalny"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = True
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("F2").Select
ActiveCell.FormulaR1C1 = "a"
Range("G2").Select
ActiveCell.FormulaR1C1 = "b"
Range("E3").Select
ActiveCell.FormulaR1C1 = "32"
Range("F3").Select
ActiveCell.FormulaR1C1 = "5"
Range("G3").Select
ActiveCell.FormulaR1C1 = "6"
Range("E1:G3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("C2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]/(R3C[3]*R3C[4]))*1000"
Range("C2").Select
Selection.AutoFill Destination:=Range("$C$2:$C$102"), Type:=xlFillDefault
Range("C2:C102").Select
ActiveWindow.SmallScroll Down:=-90
Range("D2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]/R3C[1])*100"
Range("D2").Select
Selection.AutoFill Destination:=Range("$D$2:$D$102"), Type:=xlFillDefault
Range("D2:D102").Select
ActiveWindow.SmallScroll Down:=-84
Range("D1").Select
Columns("D:D").ColumnWidth = 10.57
Columns("C:C").ColumnWidth = 10.43
Range("D1").Select
ActiveCell.FormulaR1C1 = "epsilon [%]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "sigma [Mpa]"
Range("C2:D102").Select
ActiveWindow.SmallScroll Down:=-84
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("$C$2:$D$102")
ActiveSheet.Shapes("Wykres 1").IncrementLeft 108.75
ActiveSheet.Shapes("Wykres 1").IncrementTop -111
ActiveChart.FullSeriesCollection(1).Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).XValues = Range("$D$2:$D$102")
ActiveChart.FullSeriesCollection(1).XValues = Range("$D$2:$D$102")
ActiveChart.FullSeriesCollection(1).Values = Range("$C$2:$C$102")
ActiveChart.FullSeriesCollection(1).Name = "=""Wykres rozciągania"""
ActiveSheet.ChartObjects("Wykres 1").Activate
ActiveSheet.Shapes("Wykres 1").IncrementLeft 3
ActiveSheet.Shapes("Wykres 1").IncrementTop -0.75
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "Epsilon [%]"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
Selection.Caption = "Sigma [Mpa]"
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Trendlines.Add
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
End With
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 0.75
End With
Application.CommandBars("Format Object").Visible = False
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
End Sub