using a range with isnumber property
 

i would like to know if i there is a way i could use either IsBlank o
IsNumber in this.
I have a new range every iteration, and i just want the program to ski
the range if there is no numbers entered in it. Otherwise, the progra
(upon execution) gives an error for the StDev function because there i
nothing in the range to calculate.

here is the code so far... thank you for helping!

Sub calc_mean()
Dim J As Integer
Dim K As Integer
Dim rng As Range

' Calculation of standard deviation and mean.

For J = 4 To 192
For K = 0 To 7
Set rng = Range("D" & J + K, "O" & J + K)
If Application.WorksheetFunction.IsNumber(rng) = True Then

Cells(J + K, "R") = Application.WorksheetFunction.Average(rng)
Cells(J + K, "S") = Application.WorksheetFunction.StDev(rng)
End If

Next K
J = J + 10
Next J
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

using a range with isnumber property
 

For J = 4 To 192
For K = 0 To 7
Set rng = Range("D" & J + K, "O" & J + K)
If Application.WorksheetFunction.Count(rng) 0 Then

Cells(J + K, "R") = Application.WorksheetFunction.Average(rng)
Cells(J + K, "S") = Application.WorksheetFunction.StDev(rng)
End If

Next K

Count will tell you how many numbers are in the range.

--
Regards,
Tom Ogilvy


"chick-racer" wrote in message
...

i would like to know if i there is a way i could use either IsBlank or
IsNumber in this.
I have a new range every iteration, and i just want the program to skip
the range if there is no numbers entered in it. Otherwise, the program
(upon execution) gives an error for the StDev function because there is
nothing in the range to calculate.

here is the code so far... thank you for helping!

Sub calc_mean()
Dim J As Integer
Dim K As Integer
Dim rng As Range

' Calculation of standard deviation and mean.

For J = 4 To 192
For K = 0 To 7
Set rng = Range("D" & J + K, "O" & J + K)
If Application.WorksheetFunction.IsNumber(rng) = True Then

Cells(J + K, "R") = Application.WorksheetFunction.Average(rng)
Cells(J + K, "S") = Application.WorksheetFunction.StDev(rng)
End If

Next K
J = J + 10
Next J
End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

using a range with isnumber property
 

mr ogilvy, i really appreciate the help you provide.

THANK YOU!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/