DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
. Hi senior “Colleagues”!,
.
. One way I am learning my Excel / VBA is by answering Threads here.
. I answered one with both a Formula and a VBA Code. The code was much too complicated As I was not yet familiar enough with the method I expect is the best, the VBA Advanced Filter Method. Particularly I do not have the experience yet in getting the correct CriteriaRange
. can someone help?
. So, a simplified example.. I start with this.
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]
……………
. And wot I finally want is this.
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]
.
. Putting it into words.
. I want to list out the Unique Codes Numbers from Column A, but only if the corresponding date or dates for any of any these Code Numbers ( given in the corresponding row in Column B ) lie within the start and stop date criteria given in cells C2 and D2 respectively.
. I have one possible start point in the code I give a the end. There, in line 70, the Advanced Filter is run first without any CriteriaRange, with unique argument set to true,
Unique:=True
. … this gives me a column of the unique Code numbers which I place arbitrarily starting in cell C10.
. I expect the next step would be to complete the missing bit in my argument for the critical Range,
CriteriaRange:=Range("C10:________,
. …. To run the Advanced Filter a second time In my line 90.
. In addition I would need the appropriate combination of headings, and or not headings in a the critical range which has cell C10 as its Top left corner. Also I need the exact format / syntax that I put in a few rows starting at row 10 and being within the specified Critical Range.
. Possibly some other Critical Range could do away with my code line 70 ?
…………………………………………..
. I have a non AdvancedFilterMethod VBA code, and I would like a VBA AdvancedFilterMethod version. But of course if anyone “feels the urge to do some other VBA Code or a better formula then me then please do so here or in that Thread
http://www.mrexcel.com/forum/excel-...ique-values-depending-date-adjacent-cell.html
. Any response here which I use I will, of course, a-credit the worthy!!
Many Thanks
. Alan
……………………………………
Here my start attempt at the Advanced Filter Method VBA code
In case it helps, particularly as getting the correct critical range criteria across in a screenshot can be tricky I enclose the File I am playing with just now , should you have the time to view, and possible put a solution in for me.. I will paste appropriate screen shots so everyone can follow anything you do..
https://app.box.com/s/kffotse2fsqoifavx2w3whpra6w4jjqd
.
. One way I am learning my Excel / VBA is by answering Threads here.
. I answered one with both a Formula and a VBA Code. The code was much too complicated As I was not yet familiar enough with the method I expect is the best, the VBA Advanced Filter Method. Particularly I do not have the experience yet in getting the correct CriteriaRange
. can someone help?
. So, a simplified example.. I start with this.
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]
A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][td]F
[/td][/tr][tr][td]
1
[/td][td]Code[/td][td]Date[/td][td]startdate[/td][td]stopdate[/td][td][/td][td][/td][/tr][tr][td]
2
[/td][td]3104
[/td][td]4/18/2015[/td][td]4/21/2015[/td][td]4/25/2015[/td][td][/td][td][/td][/tr][tr][td]
3
[/td][td]3599
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
4
[/td][td]4004
[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
5
[/td][td]7158
[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
6
[/td][td]7158
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
7
[/td][td]7158
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
8
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]
……………
. And wot I finally want is this.
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]
A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][td]F
[/td][/tr][tr][td]
1
[/td][td]Code[/td][td]Date[/td][td]startdate[/td][td]stopdate[/td][td][/td][td]Code[/td][/tr][tr][td]
2
[/td][td]3104
[/td][td]4/18/2015[/td][td]4/21/2015[/td][td]4/25/2015[/td][td][/td][td]3599[/td][/tr][tr][td]
3
[/td][td]3599
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td]7158[/td][/tr][tr][td]
4
[/td][td]4004
[/td][td]4/15/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
5
[/td][td]7158
[/td][td]4/25/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
6
[/td][td]7158
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
7
[/td][td]7158
[/td][td]4/23/2015[/td][td][/td][td][/td][td][/td][td][/td][/tr][/table][Table="width:, class:grid"][tr][td]JL DataAdvFilt[/td][/tr][/table]
.
. Putting it into words.
. I want to list out the Unique Codes Numbers from Column A, but only if the corresponding date or dates for any of any these Code Numbers ( given in the corresponding row in Column B ) lie within the start and stop date criteria given in cells C2 and D2 respectively.
. I have one possible start point in the code I give a the end. There, in line 70, the Advanced Filter is run first without any CriteriaRange, with unique argument set to true,
Unique:=True
. … this gives me a column of the unique Code numbers which I place arbitrarily starting in cell C10.
. I expect the next step would be to complete the missing bit in my argument for the critical Range,
CriteriaRange:=Range("C10:________,
. …. To run the Advanced Filter a second time In my line 90.
. In addition I would need the appropriate combination of headings, and or not headings in a the critical range which has cell C10 as its Top left corner. Also I need the exact format / syntax that I put in a few rows starting at row 10 and being within the specified Critical Range.
. Possibly some other Critical Range could do away with my code line 70 ?
…………………………………………..
. I have a non AdvancedFilterMethod VBA code, and I would like a VBA AdvancedFilterMethod version. But of course if anyone “feels the urge to do some other VBA Code or a better formula then me then please do so here or in that Thread
http://www.mrexcel.com/forum/excel-...ique-values-depending-date-adjacent-cell.html
. Any response here which I use I will, of course, a-credit the worthy!!
Many Thanks
. Alan
……………………………………
Here my start attempt at the Advanced Filter Method VBA code
Code:
[color=blue]Sub[/color] AdvancedFilterMethod()
10 [color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("JL data") [color=lightgreen]'[/color]
20
30 [color=blue]Dim[/color] lastrow [color=blue]As[/color] Long: [color=blue]Let[/color] lastrow = wks1.Cells(Rows.Count, "A").End(xlUp).Row
40 [color=lightgreen]'[/color]
50 [color=blue]Dim[/color] rngCode [color=blue]As[/color] Range: [color=blue]Set[/color] rng = wks1.Range("A1:A" & lastrow & "")
60 [color=lightgreen]'AdvancedFilter Run 1 to get Unique Codes for critical range in Run 2[/color]
70 rngCode.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks1.Range("C10"), Unique:=[color=blue]True[/color]
80 [color=lightgreen]'AdvancedFilter Run 2[/color]
90 rngCode.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("C10:________, CopyToRange:=wks1.Range("F2")
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'AdvancedFilterMethod()[/color]
In case it helps, particularly as getting the correct critical range criteria across in a screenshot can be tricky I enclose the File I am playing with just now , should you have the time to view, and possible put a solution in for me.. I will paste appropriate screen shots so everyone can follow anything you do..
https://app.box.com/s/kffotse2fsqoifavx2w3whpra6w4jjqd