Tdude
12-17-2012, 08:03 AM
Hello everybody,
the following code applies the stdev. to a dynamic range of 20 cells.
Is there a more elegant and less messy way to code a dynamic range?
I mean, withouth using the offsetting property 20 times (in this example)
I tryied something like worksheetfunction.stdev(Range("first offsetting cell, and last") )but it does not seem to be working.
thanks in advance for your kind help
Tdude
Sub Stdev()
For c = 26 To 36
Cells(24, c).Select
Do While ActiveCell.Offset(0, -12) <> ""
ActiveCell.Value = Application.WorksheetFunction.Stdev(ActiveCell.Offset(-19, -12).Value, ActiveCell.Offset(-18, -12).Value, ActiveCell.Offset(-17, -12).Value, _
ActiveCell.Offset(-16, -12).Value, ActiveCell.Offset(-15, -12).Value, ActiveCell.Offset(-14, -12).Value, ActiveCell.Offset(-13, -12).Value, _
ActiveCell.Offset(-12, -12).Value, ActiveCell.Offset(-11, -12).Value, ActiveCell.Offset(-10, -12).Value, ActiveCell.Offset(-9, -12).Value, ActiveCell.Offset(-8, -12).Value, _
ActiveCell.Offset(-7, -12).Value, ActiveCell.Offset(-6, -12).Value, ActiveCell.Offset(-5, -12).Value, ActiveCell.Offset(-4, -12).Value, ActiveCell.Offset(-3, -12).Value, _
ActiveCell.Offset(-2, -12).Value, ActiveCell.Offset(-1, -12).Value, ActiveCell.Offset(0, -12).Value)
ActiveCell.Offset(1, 0).Select
Loop
Next c
End Sub
the following code applies the stdev. to a dynamic range of 20 cells.
Is there a more elegant and less messy way to code a dynamic range?
I mean, withouth using the offsetting property 20 times (in this example)
I tryied something like worksheetfunction.stdev(Range("first offsetting cell, and last") )but it does not seem to be working.
thanks in advance for your kind help
Tdude
Sub Stdev()
For c = 26 To 36
Cells(24, c).Select
Do While ActiveCell.Offset(0, -12) <> ""
ActiveCell.Value = Application.WorksheetFunction.Stdev(ActiveCell.Offset(-19, -12).Value, ActiveCell.Offset(-18, -12).Value, ActiveCell.Offset(-17, -12).Value, _
ActiveCell.Offset(-16, -12).Value, ActiveCell.Offset(-15, -12).Value, ActiveCell.Offset(-14, -12).Value, ActiveCell.Offset(-13, -12).Value, _
ActiveCell.Offset(-12, -12).Value, ActiveCell.Offset(-11, -12).Value, ActiveCell.Offset(-10, -12).Value, ActiveCell.Offset(-9, -12).Value, ActiveCell.Offset(-8, -12).Value, _
ActiveCell.Offset(-7, -12).Value, ActiveCell.Offset(-6, -12).Value, ActiveCell.Offset(-5, -12).Value, ActiveCell.Offset(-4, -12).Value, ActiveCell.Offset(-3, -12).Value, _
ActiveCell.Offset(-2, -12).Value, ActiveCell.Offset(-1, -12).Value, ActiveCell.Offset(0, -12).Value)
ActiveCell.Offset(1, 0).Select
Loop
Next c
End Sub