Function to do averange....

[pedie]

Hi, can someone please help me replicate this code to do average instead of sum?
something like this so that i dont have to use indirect...

=MyAverage(Sheet1:Sheet4,A2)

Thanks in advance for helping...

Code written by Domenic.

[/FONT]
[FONT=Courier New][COLOR=#00008b]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Function[/COLOR] MySum(FirstSht [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], LastSht [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Cell [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Temp As [COLOR=darkblue]String[/COLOR]
    Application.Volatile
    [COLOR=darkblue]If[/COLOR] Worksheets(LastSht).Index >= Worksheets(FirstSht).Index [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'Do nothing[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        Temp = FirstSht
        FirstSht = LastSht
        LastSht = Temp
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = Worksheets(FirstSht).Index [COLOR=darkblue]To[/COLOR] Worksheets(LastSht).Index
        MySum = MySum + Worksheets(i).Range(Cell.Address)
    [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[shg]

Look in your other thread, pedie.