Excel

Derivative (macro)

Sub Derivate()
' Calculate derivative of data table containing the selected cell
' Be careful with adjacent (nearby) columns
		Selection.CurrentRegion.Select
		Set range1 = Selection
		Set Selection1 = range1.Cells(1, 1)
		Set Selection1bis = Selection1.Offset(1, 0)
		Set Selection2 = Selection1.End(xlDown)
		Set Range2 = Range(Selection1, Selection2)
		Set Range2bis = Range(Selection1bis.Range("A1"), Selection2)
		Range2.Select
		If Selection2.Row < 10000 Then
		n_lignes = range1.Rows.Count
		n_colonnes = range1.Columns.Count - 1
		If n_colonnes < 10000 Then
		ActiveCell.Offset(0, n_colonnes + 1).Range("A1").Select
		Set Selection3 = Selection
		ActiveCell.Offset(1, 0).Range("A1").Select
		For i_colonne = 1 To n_colonnes
		entete = 0
		If WorksheetFunction.IsText(Selection.Offset(-1, -n_colonnes)) Then
		entete = 1
		ActiveCell.Offset(-1, 0).Range("A1").FormulaR1C1 = "=CONCATENATE(""" & ActiveCell.Offset(-1, -n_colonnes).Value & """, "" derivative"")"
		ActiveCell.Offset(1, 0).Range("A1").Select
		End If
		ActiveCell.Range("A1").FormulaR1C1 = _
				"=IF(AND(ISNUMBER(R[-1]C[-" & n_colonnes + i_colonne & "]),ISNUMBER(R[1]C[-" & n_colonnes + i_colonne & "]),ISNUMBER(R[-1]C[-" & n_colonnes & "]),ISNUMBER(R[1]C[-" & n_colonnes & "])),(R[1]C[-" & n_colonnes & "]-R[-1]C[-" & n_colonnes & "])/(R[1]C[-" & n_colonnes + i_colonne & "]-R[-1]C[-" & n_colonnes + i_colonne & "]),NA())"
		ActiveCell.Select
		Set Selection4 = Selection
		Selection.Copy
		Set Selection5 = Selection.Offset(Selection2.Row - Selection1bis.Row - entete - 1, 0)
		Range(Selection, Selection5).Select
		ActiveSheet.Paste
		Selection4.Offset(-entete, 0).Select

		ActiveCell.Offset(0, 1).Range("A1").Select


		Next i_colonne

		Application.CutCopyMode = False
		Set range3 = Range(Selection3, Selection5)
		range3.Select

		ActiveSheet.Shapes.AddChart.Select
		ActiveChart.PlotArea.Select
		ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).XValues = Range2bis
		ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
		ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = Selection1

		Else
		MsgBox "Empty selection!"
		Range("A1").Activate
		End If
		Else
		MsgBox "Empty selection!"
		Range("A1").Select
		End If
End Sub