Prev: Excel 2007 VBA shape fill colour problem
Next: Excel 2003 VBA Phantom Breakpoints
From: p45cal on 4 Feb 2010 11:07

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/showthread.php?t=176334

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: p45cal on 4 Feb 2010 16:53

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/showthread.php?t=176334

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: ppeer on 5 Feb 2010 08:10
On 4 feb, 22:53, p45cal <p45cal.45v...(a)thecodecage.com> 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/showthread.php?t=176334
>
> [url=&quot;http://www.thecodecage.com"]Microsoft Office Help[/url]

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.
From: p45cal on 5 Feb 2010 09:22

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/showthread.php?t=176334

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: p45cal on 10 Feb 2010 13:04

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/showthread.php?t=176334

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

 |  Next  |  Last
Pages: 1 2
Prev: Excel 2007 VBA shape fill colour problem
Next: Excel 2003 VBA Phantom Breakpoints