Microsoft Excel - Creating a macro with autofill for varying data sets with formulas

I am new to creating macros and I need a macro that is able to determine the maximum, minimum, average and standard deviation. These names I have placed on a spreadsheet in A3-A6.

In A8 I need the Stdev+average. A9 the maximum, A10 the average, A11 the minimum and A12 the stdev-average of the column  (i.e. column b). I  have attempted to have the macro calculate these numbers for the columns starting at B14 but have thus been unsuccessful.

The ranges in both the columns and the rows will varying depending on the amount of data being analyzed.

Thank you in advanced.

Below is the code I have thus far.

Sub august4macroattemptXX()
'
' august4macroattemptXX Macro
' attempt 3 million
'

'
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Max"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Min"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Average"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Stdev"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "Stdev+Ave"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "Max"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "Ave"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "Min"
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Stdev-Ave"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[11]C:R[330]C)"
    Range("B14:B1398").Select
    ActiveCell.FormulaR1C1 = "=MIN(R[10]C:R[329]C)"
    Range("B14:B1398").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[9]C:R[328]C)"
    Range("B14:B1398").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[8]C:R[327]C)"
    Range("B14:B1398").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-3]C"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=R[-6]C"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "=R[-7]C"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "=R[-6]C-R[-7]C"
    Range("B3:B6").Select
    Selection.AutoFill Destination:=Range("B3:C6"), Type:=xlFillDefault
    Range("B3:C6").Select
    Range("B3:B12").Select
    Selection.AutoFill Destination:=Range("B3:GR12"), Type:=xlFillDefault
    Range("B3:GR12").Select
    Range("GR5").Select
    ActiveWindow.ScrollColumn = 177
    ActiveWindow.ScrollColumn = 175
    ActiveWindow.ScrollColumn = 174
    ActiveWindow.ScrollColumn = 173
    ActiveWindow.ScrollColumn = 172
    ActiveWindow.ScrollColumn = 171
    ActiveWindow.ScrollColumn = 170
    ActiveWindow.ScrollColumn = 169
    ActiveWindow.ScrollColumn = 167
    ActiveWindow.ScrollColumn = 166
    ActiveWindow.ScrollColumn = 164
    ActiveWindow.ScrollColumn = 163
    ActiveWindow.ScrollColumn = 162
    ActiveWindow.ScrollColumn = 160
    ActiveWindow.ScrollColumn = 159
    ActiveWindow.ScrollColumn = 158
    ActiveWindow.ScrollColumn = 157
    ActiveWindow.ScrollColumn = 154
    ActiveWindow.ScrollColumn = 153
    ActiveWindow.ScrollColumn = 152
    ActiveWindow.ScrollColumn = 149
    ActiveWindow.ScrollColumn = 146
    ActiveWindow.ScrollColumn = 143
    ActiveWindow.ScrollColumn = 140
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 131
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 78
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 62
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
End Sub

Hi Lucy:

I can't figure out what you are trying to display in cells B9, B10, B11 and B12, but take a look at this code and see whether you can adapt it to your needs.


Hope this helped,
Rolf

Hello Rolf,

Thank you. It worked perfectly for Stdev+Ave. I was hoping to have the same work for max, ave, min and stdev-ave.
Do macro's work across columns with varying data? I noticed that the code included  " .End(x1Up) "  to include the whole column. Is there a way to do that across to include rows B- RR (for example- as the letter will change depending on the amount of data).

I attempted to change the code to what I thought might work but was unsuccessful.

Sub august4macroattempt2_RPJ()
   
  Range("A3").Value = "Max"
  Range("A4").Value = "Min"
  Range("A5").Value = "Average"
  Range("A6").Value = "Stdev"
  Range("A8").Value = "Stdev+Ave"
  Range("A9").Value = "Max"
  Range("A10").Value = "Ave"
  Range("A11").Value = "Min"
  Range("A12").Value = "Stdev-Ave"
   
  Dim db As Range
  Set db = Range(Range("B14"), Range("B" & Rows.Count).End(xlUp))
 
  Range("B3").Value = WorksheetFunction.Max(db)
  Range("B4").Value = WorksheetFunction.Min(db)
  Range("B5").Value = WorksheetFunction.Average(db)
  Range("B6").Value = WorksheetFunction.StDev(db)
  Range("B8").Value = WorksheetFunction.StDev(db) + Range("b5").Value
  Range("B9").Value = WorksheetFunction.Max("b3").Value
  Range("B10").Value = WorksheetFunction.Ave("b5").Value
  Range("B11").Value = WorksheetFunction.Min("b4").Value
  Range("B12").Value = WorksheetFunction.StDev(db) - Range("b5").Value
End Sub

Thank you so much for your help, I have been at this for hours and it is such a relief to actually see it working! :)

Hello Rolf,

 In B9 I am trying to show the maximum of the values from B14- the end of that column.
B10 is the average of the B column (from B14 and below), B11 is the minimum (of B14 and below), and B12 is the standard deviation of that column plus the average of the column. The goal of the spreadsheet is to make a box and whisker chart.

THank you for your help thus far I really appreciate it.

Hi Lucy:

I am still not clear on what you are trying to do with cells B9 through B12. However the answer to your question whether this approach could be expanded to more than one column is very straightforward. Substitute the Set db statement from the code I previously posted with this one:


Of course this requires that column RR is at least as long as any other column in this range.

Hope this helped,
Rolf

Hello Rolf,

I inserted the code but an error appeared and I am not sure what I need to do to fix it.  The error states "Compile error: expected: list seperator or )". I have tried a number of different locations to place seperators and ), but non have worked thus far.

Below is the code that I have so far.

Thank you.

Sub august4macroattempt2_RPJ()
   
  Range("A3").Value = "Max"
  Range("A4").Value = "Min"
  Range("A5").Value = "Average"
  Range("A6").Value = "Stdev"
  Range("A8").Value = "Stdev+Ave"
  Range("A9").Value = "Max"
  Range("A10").Value = "Ave"
  Range("A11").Value = "Min"
  Range("A12").Value = "Stdev-Ave"
   
  Dim db As Range
  Set db = Range(Range("B14"), Range("B" & Rows.Count).End(xlUp))
 
  Range("B3").Value = WorksheetFunction.Max(SetAttr db = Range(Range("B14"), Range["RR" & Rows.Count].End(xlUp)]
  Range("B4").Value = WorksheetFunction.Min(Set db = Range(Range("B14"), Range("RR" & Rows.Count).End(xlUp))
  Range("B5").Value = WorksheetFunction.Average(Set db = Range(Range("B14"), Range("RR" & Rows.Count).End(xlUp))
  Range("B6").Value = WorksheetFunction.StDev(Set db = Range(Range("B14"), Range("RR" & Rows.Count).End(xlUp))
  Range("B8").Value = WorksheetFunction.StDev(Set db = Range(Range("B14"), Range("RR" & Rows.Count).End(xlUp)) + Range("b5").Value
  Range("B9").Value = WorksheetFunction.Max("b3").Value
  Range("B10").Value = WorksheetFunction.Ave("b5").Value
  Range("B11").Value = WorksheetFunction.Min("b4").Value
  Range("B12").Value = WorksheetFunction.StDev(db) - Range("b5").Value
End Sub

Hi Lucy:

the particular 'Set db = ...' statement I sent you was intended to span a data range from column B through column RR (which requires Excel2007) AND, as I said, requires that there are data in column RR from RR14 down.

Alternatively, AS LONG AS column A AND row 12 are empty you could also give this statement a try:

Hope this helped,
Rolf