EXCEL MACRO problem

Excel Excel 2007
June 1, 2011 at 03:31:05
Specs: Windows XP, 1Gb / 4.2 Ghz
Hi
This macro works in Excel 2003 but fails in the 2007 version.
Sub Counter()
Range("SlideData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("CounterCrit"), CopyToRange:=Range("CounterExt"), Unique:=False
Range("AnalystExtract").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("RollMeanCrit"), CopyToRange:=Range("RollMeanExt"), Unique:=False
End Sub

When run in Excel 2007 it returns no data for the first Advanced Filter - leaving no way to see if the second Advanced Filter has also failed.
When run in Excel 2003 it returns data according to the criteria as follows:

The first Advanced Filter copies all records from the Range: 'SlideData'
to CopyToRange: 'AnalystExt'
according to the CriteriaRange: 'AnalystCrit'

The second Advanced Filter copies all records from the Range: 'AnalystExtract'
to CopyToRange: 'RollMeanExt'
according to the CriteriaRange: 'RollMeanCrit'

The macro runs entirely in Sheet2.

The range names for the first Advanced Filter are:
SlideData =Sheet1!$A$20:$P$1000
AnalystExt =Sheet2!$AB$30:$AH$30
AnalystCrit =Sheet2!$A$2:$C$3

The range names for the second Advanced Filter are:
AnalystExtract =Sheet2!$AA$30:$AH$330
RollMeanExt =Sheet2!$B$30:$H$30
RollMeanCrit =Sheet2!$AA$25:$AA$26

I'm a long time user of Excel.


See More: EXCEL MACRO problem

Report •


#1
June 1, 2011 at 04:32:17
In order to duplicate your setup, please provide some example data for each range and criteria. I'd especially like to see the lay out the criteria ranges and then just a few pieces of the data you are filtering.

Click on the following line for instructions on how to post data and code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 2, 2011 at 03:48:24
Hi !!!deleted and restarted!!!

Report •

#3
June 2, 2011 at 04:07:22
Hi
Sorry about the delay in getting back - I was committed to attend a meeting last night of the Irwell Writers Group, I am also helping to run the Daily Telegraph Monthly Short Story Competition and of course it's month end. Back to the spreadsheet - which works fine on an old PC running Excel 2003. Its purpose is to run statistical tests on observations. What the macro does is extract a defined section of filtered data. Due to the vagaries of the data collection method, some attempt must be made to anticipate data gaps.
For this reason I have two filters the second one being instrumental in trimming excess data - a precise amount of data is essential for calculating a Rolling Mean (A Fuzzy to Precise Process)


Here are some sample entries.

I - The First Advanced Filter.

Range name: SlideData (This is the only range on Sheet1)

Row	D	   E	   F	 G	  H	 I 	 J	 K	 L	
20	Counter	  Date	 Slide	Diameter Fibres	Fields	f/mm²	mean	 ESD
21	tp1	15/01/07   3	100 	  8 	200 	0.01 	 5.00 	 2.45 
22	tp1	15/01/07   5	100 	 40 	200 	0.04 	21.34 	 6.29 
23	tp1	15/01/07  15	100 	  7 	200 	0.01 	 3.98 	 2.15 
24	tp1	15/01/07  19	100 	 65 	200 	0.07 	53.12 	12.88 
25	tp3	15/01/07   3	100 	  8 	200 	0.01 	 5.00 	 2.45 
26	tp3	15/01/07   5	100 	 40 	200 	0.04 	21.34 	 6.29 
27	tp3	15/01/07  15	100 	  8 	200 	0.01 	 3.98 	 2.15 
28	tp3	15/01/07  19	100 	 81 	200 	0.08 	53.12 	12.88 
29	tp2	17/01/07   3	100 	  6 	200 	0.01 	 5.00 	 2.45 
30	tp2	17/01/07   5	100 	 27 	200 	0.03 	21.34 	 6.29 
31	tp2	17/01/07  15	100 	  6 	200 	0.01 	 3.98 	 2.15 
32	tp2	17/01/07  19	100 	 85 	200 	0.09 	53.12 	12.88 
33	tp2	23/02/07   4	100 	 27 	200 	0.03 	15.16 	 4.93 
34	tp2	23/02/07   8	100 	  8 	200 	0.01 	 2.83 	 1.78 
35	tp2	23/02/07  16	100 	  9 	200 	0.01 	 4.20 	 2.22 
36	tp2	23/02/07  20	100 	 94 	200 	0.10 	51.53 	12.56 
37	tp1	28/02/07   4	100 	 20.5 	200 	0.02 	15.16 	 4.93 
38	tp1	28/02/07   8	100 	  5 	200 	0.01 	 2.83 	 1.78 
39	tp1	28/02/07  16	100 	  7 	200 	0.01 	 4.20 	 2.22 
40	tp1	28/02/07  20	100 	 66 	200 	0.07 	51.53 	12.56 

Range name AnalystCrit
Row     A	   B	       C
2     Date	  Date	    Counter
3   >=01/01/07	<=31/12/07    tp1

Range name AnalystExt - the extract location for the first Advanced Filter.

Row   AB    AC	  AD	 AE	 AF	 AG	 AH
30   Date  Slide fibres	Fields	f/mm²	Mean	ESD

II - The Second Advanced Filter

Range name: AnalystExtract

Row 	 AA 	   AB     AC	  AD	   AE	 AF	 AG	 AH	 AI
20	Count	  Date	 Slide	Diameter Fibres	Fields	f/mm²	mean	 ESD
21	  1	15/01/07   3	  100 	   8 	 200 	0.01 	 5.00 	 2.45 
22	  2 	15/01/07   5	  100 	  40 	 200 	0.04 	21.34 	 6.29 
23	  3   etc see note on column AA below

Column AA is a calculated field that will result in either an integer or 0.


Range name: RollMeanCrit

Row   AA
25   Count
26    <>0

Range name: RollMean Ext - the extract location for the second Advanced Filter.

Row 	   B      C	   D	   E	  F	 G	  H	  I
20	  Date	 Slide	Diameter Fibres	Fields	f/mm²	mean	 ESD

My work around to this is corresponding changes to the settings on Sheet 2 and the macro. This would result an additional macro and an additional spreadsheet page - an ugly and inelegant solution which would be much more prone to error. Alternatively I could just stick with Excel 2003.


Report •
Related Solutions


Ask Question