Yeah, by time I wrote the code, I wasn't paying attention to the OP's
note.
I like the shorthand on the Offset.
It isn't really a shorthand for Offset. The calls the hidden
[_Default] method of a Range. Unlike Offset, this method uses 1-based
indices, rather than 0-based indices. E.g., R(2,1) = R.Offset(1,0).
Alan Beban (an erstwhile MVP) got me started on it years ago, and I've
adopted it over the years.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
On Thu, 4 Feb 2010 14:31:19 -0500, "JLGWhiz"
wrote:
Hi Chip, I like the shorthand on the Offset. Took me a minute to figure
out what it was. The OP was looking for average in column D, I think this
shorthand method of offset is giving him column C.
"Chip Pearson" wrote in message
.. .
Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.
Sub AAA()
Dim R As Range
Dim R2 As Range
Dim N As Long
Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
R2(1, 7).Resize(N + 1, 1) = _
"Average of '" & R.Text & "' = " & _
Application.WorksheetFunction.Average( _
R2(1, 3).Resize(N + 1, 1))
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Sub
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
On Thu, 4 Feb 2010 05:13:32 -0800 (PST), ppeer
wrote:
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