About Us

Kris Taylor

Ron Rosenfeld wrote in message . ..
On 2 Jun 2004 08:01:19 -0700, (Kris Taylor) wrote:

Thanks for your help thus far. For some reason however, the macro
seems to be giving me a Type Mismatch error. On the first page, it
gives me 47 averages, then stops, the second page gives me around 10
and the third page gives me nothing.

I went through it and made sure there were no spaces, formatted so
that everything was a number and I still have the same problem...

Any suggestions? Is my spreadsheet too big?

Definitely need some troubleshooting.

I can't imagine that your SS is too big for this to work.

So let's see what we can find out.

Are you using the routine exactly as I posted it, or did you make some
modifications? If you did make any modifications, post back here the code you
are using.

On what line does it give the Type Mismatch error?

When that happens, float your cursor over the various variables and let me know
what's in them.

i =
Result(i) =
c.value =
Result(0) =

--ron

Ron, for each spreadsheet, here is the requested data:

1)

i=6615
Result(i)=Subscript out of range
c.value=11.82
Result(0)=24.54

2)

i=6084
Result(i)=Subscript out of range
c.value=13.05
Result(0)=16.67

3)

i= 10379
Result(i)= Subscript Out Of Range
c.value= 0
Result(0)= 33.99

My code currently looks like:

Option Explicit
Sub AvgGT15()
Dim AOI As Range
Dim StoreResult As Range
Dim c As Range
Dim i As Long
Dim Result()

Set StoreResult = [M1]
Set AOI = [D1:D55000]
[M1:M55000].ClearContents

i = 0
ReDim Preserve Result(i)

For Each c In AOI
If c.Value < 15 Then
If Result(0) = 15 Then
StoreResult.Value = Application.WorksheetFunction.Average(Result())
Set StoreResult = StoreResult.Offset(1, 0)
End If
i = 0
Result(0) = 0
Else
ReDim Preserve Result(i)
Result(i) = c.Value
i = i + 1
End If
Next c

If Result(0) = 15 Then StoreResult.Value = _
Application.WorksheetFunction.Average(Result())

End Sub

Thanks,

Kris Taylor

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sort, select and average macro help	Amy	Excel Discussion (Misc queries)	2	February 5th 10 07:01 PM
Find monthly average but have average automatically configured	kimbafred	Excel Discussion (Misc queries)	2	August 8th 07 12:28 AM
moving the formula "average" over one column in a macro	drumstu	Excel Worksheet Functions	1	August 23rd 05 08:01 PM
Average Macro	Christopher Anderson	Excel Discussion (Misc queries)	2	December 22nd 04 06:43 PM
Variable sized average macro/function	danwtf2004	Excel Programming	2	January 29th 04 10:11 PM

Menu

About Us