WorksheetFunction
 

Hi,

does someone have an idea why I can't run WorksheetFunction?? I have a code
as below, but I get errormessages for WorksheetFunctions..

Sub prov()
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If myrange1 Is Nothing Then
Set myrange1 = c
Else
Set myrange1 = Union(myrange1, c)
End If
End If
redaverage = WorksheetFunction.Average(myrange1)
maxvalue = WorksheetFunction.max(myrange1)
medianvalue = WorksheetFunction.Median(myrange1)
Next
End Sub

WorksheetFunction
 

Hi,

You haven't defined what MyRange is and your tring to evaluate the result
before the code has finished running. This uses the activesheet usedrange

Sub prov()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ActiveSheet.UsedRange
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
redaverage = WorksheetFunction.Average(MyRange1)
maxvalue = WorksheetFunction.Max(MyRange1)
medianvalue = WorksheetFunction.Median(MyRange1)

End Sub

Mike

"Ksenija" wrote:

Hi,

does someone have an idea why I can't run WorksheetFunction?? I have a code
as below, but I get errormessages for WorksheetFunctions..

Sub prov()
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If myrange1 Is Nothing Then
Set myrange1 = c
Else
Set myrange1 = Union(myrange1, c)
End If
End If
redaverage = WorksheetFunction.Average(myrange1)
maxvalue = WorksheetFunction.max(myrange1)
medianvalue = WorksheetFunction.Median(myrange1)
Next
End Sub

WorksheetFunction
 

Thanks:-)!!!!!!! evrything s working now!!!

"Mike H" skrev:

Hi,

You haven't defined what MyRange is and your tring to evaluate the result
before the code has finished running. This uses the activesheet usedrange

Sub prov()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ActiveSheet.UsedRange
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
redaverage = WorksheetFunction.Average(MyRange1)
maxvalue = WorksheetFunction.Max(MyRange1)
medianvalue = WorksheetFunction.Median(MyRange1)

End Sub

Mike

"Ksenija" wrote:

Hi,

does someone have an idea why I can't run WorksheetFunction?? I have a code
as below, but I get errormessages for WorksheetFunctions..

Sub prov()
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If myrange1 Is Nothing Then
Set myrange1 = c
Else
Set myrange1 = Union(myrange1, c)
End If
End If
redaverage = WorksheetFunction.Average(myrange1)
maxvalue = WorksheetFunction.max(myrange1)
medianvalue = WorksheetFunction.Median(myrange1)
Next
End Sub

WorksheetFunction
 

Glad I could help

"Ksenija" wrote:

Thanks:-)!!!!!!! evrything s working now!!!

"Mike H" skrev:

Hi,

You haven't defined what MyRange is and your tring to evaluate the result
before the code has finished running. This uses the activesheet usedrange

Sub prov()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ActiveSheet.UsedRange
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
redaverage = WorksheetFunction.Average(MyRange1)
maxvalue = WorksheetFunction.Max(MyRange1)
medianvalue = WorksheetFunction.Median(MyRange1)

End Sub

Mike

"Ksenija" wrote:

Hi,

does someone have an idea why I can't run WorksheetFunction?? I have a code
as below, but I get errormessages for WorksheetFunctions..

Sub prov()
For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If myrange1 Is Nothing Then
Set myrange1 = c
Else
Set myrange1 = Union(myrange1, c)
End If
End If
redaverage = WorksheetFunction.Average(myrange1)
maxvalue = WorksheetFunction.max(myrange1)
medianvalue = WorksheetFunction.Median(myrange1)
Next
End Sub