Microsoft Excel - Macro
Asked By Lucy R
05-Aug-10 01:09 PM
I am trying to create a macro that is able to figure the max, min, average, stdev....and from that creating thr stdev+ave, max, ave, min, stdev-ave (allowing me to make a box and whiskerchart).
The data thoough with vary is length and the number of columns will also vary. Sometimes it will go to 2720 and across to AY and others not.
Below is the code that I have thus far but it only works for stdev+ave and for column B. I am not sure what is wrong that it does not work and how am I able to get it to cover the values across the spread sheet?
Thank you.
Sub august4macroattempt3_RPJ()
Range("A3").Value = "Max"
Range("A4").Value = "Min"
Range("A5").Value = "Average"
Range("A6").Value = "Stddev"
Range("A8").Value = "Stddev+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.StdDev(db)
Range("B8").Value = WorksheetFunction.StdDev(db) + Range("B5").Value
Range("B9").Value = WorksheetFunction.Maximum(Range("B3")).Value
Range("B10").Value = WorksheetFunction.Ave(Range("B5")).Value
Range("B11").Value = WorksheetFunction.Minimum(Range("B4")).Value
Range("B12").Value = WorksheetFunction.StdDev(db) - Range("B5").Value
End Sub
wally eye replied to Lucy R
Have you tried using:
Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
or an offset to that if you have totals below:
Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Offset(-1,0).Address)
Lucy R replied to wally eye
Hello,
I tried this code in an excel sheet. When I test it in an excel sheet it doesn't work. I have been placing random values column B from B14 down.
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.Maximum = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B4").Value = WorksheetFunction.Minimum = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B5").Value = WorksheetFunction.Average = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B6").Value = WorksheetFunction.StdDev = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B8").Value = WorksheetFunction.StdDev("B6") + Range("b5").Value
Range("B9").Value = WorksheetFunction.Maximum = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address).Value
Range("B10").Value = WorksheetFunction.Ave = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address).Value
Range("B11").Value = WorksheetFunction.Minimum = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address).Value
Range("B12").Value = WorksheetFunction.StdDev = Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address) - Range("B14:" & Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address).Value
End Sub
wally eye replied to Lucy R
Use your original Range.Value formulas, but replace the "Set db =" statement with the one I gave you earlier...
Lucy R replied to wally eye
Hello Art,
When I made the changes this is what showed up when I went to apply the macro. I was wondering if you had any suggestions?
Thank you,
Lucy
Sub august4macroattempt3_Art()
Range("A3").Value = "Max"
Range("A4").Value = "Min"
Range("A5").Value = "Average"
Range("A6").Value = "Stddev"
Range("A8").Value = "Stddev+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.Maximum(set db= Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B4").Value = WorksheetFunction.min(Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B5").Value = WorksheetFunction.Average(Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B6").Value = WorksheetFunction.StdDev(Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B8").Value = WorksheetFunction.StdDev(Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address) + Range("B5").Value
Range("B9").Value = WorksheetFunction.Maximum(Range("B3")).Value
Range("B10").Value = WorksheetFunction.Ave(Range("B5")).Value
Range("B11").Value = WorksheetFunction.Minimum(Range("B4")).Value
Range("B12").Value = WorksheetFunction.StdDev(Set db = Range("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address) - Range("B5").Value
End Sub
wally eye replied to Lucy R
More like this:
Sub august4macroattempt3_RPJ()
Range("A3").Value = "Max"
Range("A4").Value = "Min"
Range("A5").Value = "Average"
Range("A6").Value = "Stddev"
Range("A8").Value = "Stddev+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("B14:"&Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Range("B3").Value = WorksheetFunction.Max(db)
Range("B4").Value = WorksheetFunction.min(db)
Range("B5").Value = WorksheetFunction.Average(db)
Range("B6").Value = WorksheetFunction.StdDev(db)
Range("B8").Value = WorksheetFunction.StdDev(db) + Range("B5").Value
Range("B9").Value = WorksheetFunction.Maximum(Range("B3")).Value
Range("B10").Value = WorksheetFunction.Ave(Range("B5")).Value
Range("B11").Value = WorksheetFunction.Minimum(Range("B4")).Value
Range("B12").Value = WorksheetFunction.StdDev(db) - Range("B5").Value
End Sub
how can I use randbetween without repeating numbers in a set Excel Excel Worksheet Discussions WorksheetFunction (1) Microsoft Excel (1) PowerPoint (1) Excel 2003 (1) RANDBETWEEN (1) Workbook (1) COUNTIF (1) Macro (1) See JE McGimpsey's site for a solution to this problem. http: / / www.mcgimpsey.com / excel / udfs / randint.html Note: requires use of VBA Gord Dibben MS Excel MVP Increase the Maximum Number value in your Randbetween formula. For Example if you want
Unique Random Numbers Excel How can a series of unique, whole number, random numbers be generated so that no two numbers are alike? Excel Miscellaneous Discussions WorksheetFunction (1) Microsoft Excel (1) WorksheetFunction.CountIf (1) Excel (1) VB (1) ClearAndCall90 (1) BiffMicrosoft (1) FillRange (1) See this: http: / / mcgimpsey.com excel / udfs / randint.html - - Biff Microsoft Excel MVP Hi CJ, I do not remember where I
Llamar una funcion de Excel a Access Excel Hola a tod@s! Alguien me puede ayudar a decirme como o si se puede llamar una funcion de Microsoft Excel "RoundUp" o cualquier otra funcion de Microsft Excel a Microsoft Access utilizando VBA. De antemano muchas gracias! Saludos VBA - Spanish Discussions Application WorksheetFunction.RoundUp (1) Microsoft Excel (1) MiExcel.WorksheetFunction.RoundUp (1) Excel (1) VBA (1) CreateObject (1) GetObject (1) RoundUp
ms excel spreadsheet - fill in required cells before saving it Excel After creating an Excel (Excel 2003) spreedsheet, how can i get the user to fill in the necessary / required cells to fill it in is not good enough)? Or is there a way to have Excel check to make sure these cells are filled in upon saving it? - - Matt K. Excel Discussions Application.WorksheetFunction.CountA (1) WorksheetFunction (1) Microsoft Excel (1) Excel 2003 (1) Sheets (1) Excel (1) You can create a
MAX of ABS of 2 values syntax Excel Would this syntax be correct ? Var1 = WorksheetFunction.Max(WorksheetFunction.Abs (Rng1), WorksheetFunction.Abs(Rng2)) Thank you for your help, J.P. Excel Programming Discussions WorksheetFunction (1) Microsoft Excel (1) MAX (1) ABS (1) VBA (1) Rng2.Address (1) Rng1.Address (1) Rng2.Value