Macro for normalization

any tips as to where to begin?

Is this what you're after?

Sub AtoB()
    Dim i As Integer
    Dim A1 As Long
    A1 = Range("A1").Value
    For i = 2 To 20
        Range("B" & i).Value = Range("A" & i).Value / A1
    Next i
    ActiveSheet.shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$2:$B$20") 'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "Your chart title"
End Sub

Thanks for your help! How would i display the trendline and the y= mx+b equation, as well as the coefficient of determination?

Try recording those desired changes, and then pasting that code into the end of what I've given you.

I modified the code in another way. I want to find the 3 day "moving average" for the normalized column. I need help with syntax for SUM. In terms of displaying the coefficient of determination and the equation for the linear regression line, here is my attempt at coding this.

Sub Auto()
    Dim i As Integer
    Dim A1 As Long
    A1 = Range("A1").Value
    For i = 2 To 10
        Range("B" & i).Value = Range("A" & i).Value / A1
    Next i
    For j = 2 To 8
        Range("C" & j).Value = SUM(Range("B"&j).Value:Range("B"&j+2).Value))/3)
    Next j
        
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$2:$B$20") 'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).Name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "Your chart title"
    ActiveChart..Trendlines.Add(Type:=Linear,
   , DisplayEquation:=True DisplayRSquared:= _
True).

End Sub

This is my final code. One question I have, however, is how do you determine the length of a column? If I want to find the number of entries in column A, how would I do this without a for loop. Also, whenever I save this as a macro excel workbook, whenever I open it back up I cannot access my module. Why is this?

Attribute VB_Name = "Module1"
Sub Auto()
'Variables
    Dim i As Integer
    Dim A1 As Long
    Dim j As Integer
    Dim k As Integer
    Dim sum As Single

    A1 = Range("A1").Value
    For i = 1 To 10
        Range("B" & i).Value = Range("A" & i).Value / A1
    Next i

    For j = 1 To 8
        For k = j To j + 2
            sum = sum + Range("B" & k).Value
        Next k
    Range("C" & j).Value = sum / 3
    sum = 0
    Next j

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$2:$B$20") 'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).Name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "10 days normalized "
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
    Backward:=0, DisplayEquation:=True).Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
'Graphs: 3 day smooth
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$C$1:$C$8") 'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).Name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "3 days normalized "
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
    Backward:=0, DisplayEquation:=True).Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
 
End Sub

Can anyone help?

=COUNTA(A:A)

will return the number of entries in column A

hth
Ajay

You can use one of the following lines of code:

Application.WorksheetFunction.CountA(Sheets(1).Range("A:A"))

'or

Range("A" & Rows.count).End(xlUp).Row

The first one will count how many cells are in column A. If you have data in rows 1-20, but you have a blank cell in row 10, then it will return 19.

The second one tells you the lowest used row in column A. So in the above scenario it would still tell you 20. It would probably be better for your purposes.

I'm still not sure why you need to do the part with the sum and divide by 3; doesn't the moving average trendline do the same thing? Try this code:

Sub Auto()
'Variables
    Dim i As Integer
    Dim A1 As Long
    Dim nRows As Integer
    A1 = Range("A1").Value
    nRows = Range("A" & Rows.count).End(xlUp).Row
    Range("B1").Value = "Normalized Data"
    For i = 2 To nRows
        Range("B" & i).Value = Range("A" & i).Value / A1
    Next i
'Graphs: linear trendline
    ActiveSheet.shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$1:$B$" & nRows)  'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "10 days normalized "
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
    Backward:=0, DisplayEquation:=True).Select
'Graphs: 3 day smooth
    ActiveSheet.shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$B$1:$B$" & nRows) 'change "Sheet1" to your sheet name
    ActiveChart.ChartType = xlLine
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SeriesCollection(1).name = "=""Your series title"""
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Your y axis title"
    ActiveChart.ChartTitle.Text = "3 days normalized "
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:=3).Select
    Selection.name = "Your trendline name here"
End Sub

Or, if you want both trendlines in one chart, that can be arranged as well.

As for not being able to access your module... what do you mean exactly? you push Alt+F11, and what do you see?

-Joe