Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
What version of Excel (only wanted to for the most up to date sorting code - the new code will not work in older versions but the old code will work in newer versions (I think))? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output in range plus 7 columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output in range plus 7 columns
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output in range plus 7 columns
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, outputin range plus 7 columns
On 4 feb, 21:00, Chip Pearson wrote:
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, LLCwww.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- 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". |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
While waiting for the OP to answer my Q. on the version of Excel he uses, my offering was going to be: Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) For Each cll In WholeRng.Cells cll.Offset(, 7).Value = Evaluate("AVERAGE(IF(" & WholeRng.Address & "=" & cll.Address & "," & WholeRng.Offset(, 3).Address & "))") Next cll or: Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) With WholeRng.Cells(1).Offset(, 6) ..FormulaArray = "=AVERAGE(IF(R1C1:R5C1=RC[-6],R1C4:R5C4))" ..Copy WholeRng.Offset(, 6).Offset(1).Resize(WholeRng.Rows.Count - 1) WholeRng.Offset(, 6).Value = WholeRng.Offset(, 6).Value End With In either case the sorting code could have gone before or after the snippets above as they work on an unsorted list too. If there are headers at the top of the sheet the first lines would be adjusted to: Set WholeRng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
On 4 feb, 22:53, p45cal wrote:
While waiting for the OP to answer my Q. on the version of Excel he uses, my offering was going to be: Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) For Each cll In WholeRng.Cells cll.Offset(, 7).Value = Evaluate("AVERAGE(IF(" & WholeRng.Address & "=" & cll.Address & "," & WholeRng.Offset(, 3).Address & "))") Next cll or: Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) With WholeRng.Cells(1).Offset(, 6) FormulaArray = "=AVERAGE(IF(R1C1:R5C1=RC[-6],R1C4:R5C4))" Copy WholeRng.Offset(, 6).Offset(1).Resize(WholeRng.Rows.Count - 1) WholeRng.Offset(, 6).Value = WholeRng.Offset(, 6).Value End With In either case the sorting code could have gone before or after the snippets above as they work on an unsorted list too. If there are headers at the top of the sheet the first lines would be adjusted to: Set WholeRng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help I am using excel 2007. Thanks for your answer (what does OP stand for?). This also works fine. I don't experience performance differences vs the Chip Pearson version. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
ppeer;634562 Wrote: I am using excel 2007. Thanks for your answer (what does OP stand for?). This also works fine. I don't experience performance differences vs the Chip Pearson version. OP stands for you, the original poster. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, outputin range plus 7 columns
On 4 feb, 18:54, Chip Pearson wrote:
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, LLCwww.cpearson.com [email on web site] On Thu, 4 Feb 2010 05:13:32 -0800 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- 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
ppeer;639223 Wrote: 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 As far as I can see the two methods should give the same results since both use the worksheet function AVERAGE in one guise or another. What aspect of 'data handling (empty cells and strings)' is it that you like? If it's the ignoring of spaces in the leftmost column then it's a case of changing how Chip's code determines the range to process. In my case, it uses the lowest cell in the leftmost column with something in it, as determined by .End(xlup) from the bottommost cell of the whole sheet (row 1 million+ in xl2007). Before I write any code I'd like to be sure of just what aspect you like regarding handling empty cells and strings; both which cells are the ones containing strings/nothing, and how Chip's code doesn't address the problem. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
Hi p45cal,
Column D with the numbers (see OP) also contains cells with the word; "nvt". Using the average function in the worksheet itself results in the expected result: ignore those cells with nvt. But using application.worksheetfunction.average(etc....) in vba trows the error; "Unable to get the average property of the worksheet funation class, error 1004". Only using numbers in each cell of the range does give the expected result. I also tried empty cells instead of nvt, but then the average function also results in an error. I hope this is of any help. Peter |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, output inrange plus 7 columns
I see the problem now. If you have 4 cells containing 2,5,nvt,blank, what do you want the average to be? Chip's gives 3.5, both my snippets give 2.333. This is because my formula treats blank cells as a zero. Probably not what you want. Do you want to exclude both strings and empty cells from the averaging altogether? Using Evaluate in Chip's code will still cause an error so I suggest using an adaptation of his code: 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 xxx = Empty On Error Resume Next xxx = Application.WorksheetFunction.Average(R2(1, 3).Resize(N + 1, 1)) On Error GoTo 0 R2(1, 7).Resize(N + 1, 1) = "Average of '" & R.Text & IIf(IsEmpty(xxx), "' = Oops!", "' = " & xxx) N = 0 Set R2 = R(2, 1) End If Set R = R(2, 1) Loop End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334 Microsoft Office Help |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
p45cal, thanks very much
I tested it and it works fast and clean! It's a real turbocharger. Indeed I want to exclude strings and blanks from the calc, although my input cells are never blank, so no further issue he this code does just that. best rgards ppeer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select different columns within a range | Charts and Charting in Excel | |||
Select range of rows/Columns? | Excel Programming | |||
Copy range from multiple .xls files - output to separate columns per .xls file | Excel Programming | |||
Modfiy row of VB to select range of columns | Excel Programming | |||
VBA Range Issue : Cannot programatically select more than 42 columns | Excel Programming |