excelvbastdev

How to write a macro to do standard deviation on a column of data just before a blank row?


enter image description here

I have managed to work out the mean by writing this macro

'This works out how many rows (another way)
Dim AV As Long, AVC As Long
AV = Range("O" & Rows.Count).End(xlUp).Row
AVC = AV - 1

 ' This adds up all the data starting from row 2 to before the blank row
 Dim LR As Long
 myRange = ActiveSheet.Range("O2", Range("O2").End(xlDown))
 LR = WorksheetFunction.sum(myRange)
'MsgBox LR

 ' So finding the mean
 Dim mean As Long
 Range("P2").Select
 ActiveCell.FormulaR1C1 = LR / AVC
 Selection.Value = Format(ActiveCell, "#.00")

which gave the result of 17.84 in cell P2

But when I tried to write the same to calculate the standard deviation, it returned a value of 0. Does it mean that I cannot use a similar coding below to do the job?

 ' Doing stdev on range
 Dim MD As Long
 myRange = ActiveSheet.Range("O2", Range("O2").End(xlDown))
 MD = WorksheetFunction.StDev_S(myRange)
 MsgBox MD

Also, I cannot figure out how to copy and paste it and stop depending on how many rows are in column O. Really appreciate some help as I am very new to writing macros.

enter image description here


Solution

  • Change dim MD as Long to dim MD as double.