Developer Forum

Dear expert,

I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):

columns:
A B C D E F G
Aa 4 average of range column D: 5.7
Aa 3 average of range column D: 5.7
Aa 10 average of range column D: 5.7
Bb 2 average of range column D: 3
Bb 4 average of range column D: 3
etc. etc. etc. until end of column and last range

n 4 feb, 21:00, Chip Pearson < XXXX@XXXXX.COM > wrote:
> >> .CurrentRegion.Sort R, xlAscen>i>>
> >> et R> >>R
> >> o Until R.Value = vbNull>t>>ng
> >> f StrComp(R.Text, R(2, 1).Text, vbTextCompare)>=>> Then
> >> > >> = N + 1 >>>>> lse
> >> 2(1, 7>.>>size(N + 1, 1) = _
> >> Avera>e>>f '" & R.Text & "' = " & _
> >> pp>i>>tion.WorksheetFunction.Average( _
> >>>gt;> gt;gt;>(1, 3).Resize(N + 1, 1)) >>>>> gt; >>0
> >> et>R>>= R(2, 1)> > >> gt;e>>R = R(2, 1)
> >>>> oop
> >> End Sub
>
> >> >>rdially,
> >> Chip Pearson
> >> M>c>>soft MVP 1998 - 20>0>>> >> Pearson Software >on>u>>ing, LLC
> >>www.cpearson.com
> >> [email on web site]
>
> >> On>Th>,>> Feb 2010>05>1>>>2 -0800 (PST),>pp>e>>> XXXX@XXXXX.COM >
> >> wrote:
>
> >>>Dear expert,
>
> >>>I am>t>>>ng to write code for th> >>>lowing problem; so far without
> >>>succes. Can you help?
> >>>First, >o>>>on column A then selec> >>> range in column A based on the
> >>>same name (e.g. Aa).
> >>>Second> >>>culate the average of t>e>>>rresponding range (rows) but 3
> >>>columns to the right,
> >>>and Thi>d>>>t the average of>th>t>>>nge in col>m>>> (the actual output
>
> >>>columns:
> >>>A >gt;>>E
> >>>Aa 4 verage of>r>>>e column D: 5.7
> >>>Aa 3 v>r>>> of range column D: 5.7
> >>>Aa 10>gt;>>verage of range column D: 5.7
> >>>Bb > >>> average of range column D: 3
> >>>Bb average of range column D: 3
> >>>etc> gt;tc. tc. until end of column and last range- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

thanks for sharing your knowledge: it works perfectly. Didn't know
about "the R method".

> Dim R As Range> > Dim R2 As Ran>e
> Dim N As >on>
>
> Set R = Range("First>ell")
> R.CurrentRegion.Sort R, xlA>cending
> >et R2 = R
> Do Until R.Value = >bNullString
> If StrComp(R.Text, R(2, 1).Text, vbTextC>mpare) = 0 Then
>>gt;N = N + 1
> Else
> gt;R2(1, 7).Resize(N + 1, 1) = _
> > "Average of '" & R.Text & "' = " & _
> > Application.WorksheetFunction.Av>rage( _
> gt;R2(1, 3).>esize(N + 1, >))
> N = >
> gt;Set >2 > R(2, 1)
> gt;End If
>>Set R = R(2, 1)
> gt;Loop
> End Sub
>
> Cordially,
> Chip Pearson >> Microsoft MVP 1998 ->20>0
> Pearson Software Consulting, LLCwww.cpearson.com
> [email on>we> s<te]
>> >>On>Th>,>4 Feb 2010 05:>3:>2>-0800 (PST),ppeer< XXXX@XXXXX.COM >

>
>
>
> >Dear ex>e>t,
>
> >I am trying t> >rite code for the following problem; so far without
> >succes. Can you >e>p?
> >First, sort on >o>umn A then select the range in column A based on the
> >same name (e.g> >a).
> >Second, calcula>e>the average of the corresponding range (rows) but 3
> >columns to the r>g>t,
> >and Third>pu> >he average>o> that range in column>7>(the actual output
> >of the macro):
>
> >c>l>mns:
> >A
> >Aa 4 > > verage of range column D: 5.7
> >Aa > > 3 verage of range column D: 5.> >> >Aa 10 verage of range co>u>n D: 5.7
> >Bb average of range column D: 3
> >Bb average of range co>um> D: 3
> >etc. tc. tc. until end of column and last range- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Hi Chip,

After testing both methods for bulk processing of data I prefer the
Chip method for speed, but the p45cal method for data handling (empty
cells and strings).
Is there anyway Chip or p45cal, to circumvent the traditional
worksheetfunction.average method for Chips's macro?. I tried to
implement the evaluate method in Chips macro but without succes.
Help would be appreciated!

thanks