Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction help | Excel Discussion (Misc queries) | |||
WorksheetFunction OR | Excel Programming | |||
Worksheetfunction help | Excel Programming | |||
Max WorksheetFunction | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming |