Find item x in a list VBA

[Confidentjohn]

Hi

I want to control a filter in a pivot table with some VBA.

The data is a date but displayed in the following format

yyyymmdd and is represented as a number.


The pivot data looks roughly like

Date	Data
20140201	1
20140201	2
20140201	3
20140202	2
20140202	4
20140202	8
20140203	5
20140203	9
20140204	8
20140204	90
20140205	6
20140205	8
20140206	9
20140206	3
20140206	3
20140207	7
20140207	6
20140207	6
20140208	5
20140208	3

<tbody>
</tbody>

In the actuall spreadsheet this is a constantly growing list...

When pivoted the date acts as a label and is displayed as below

	20140205	20140206	20140207	20140208
Data	78	789	56	567

<tbody>
</tbody>

I would like to set some VBA that scans the list I and finds the maximum (Which i have figured out) and the x item, in the example above lets say the 4th back from the largest. But in reality will be the 28th item back.


I would then use these to dynamically set the filter between the start and end values. (This bit i can do)

Would someone help me figure out how to get the mid item so i can set a dimension with it in VBA


Hope someone can help

Cheers

John

 

[Confidentjohn]

Anyone??????

 

[Marcelo Branco]

If i understood correctly you want the 4th largest date (considering just unique values)

So assuming the dates in column A, header in row 1, maybe...

Using an array formula
=LARGE(IF(FREQUENCY(DataRange,DataRange),DataRange),4)

Confirmed with Ctrl+Shift+Enter

where DataRange is dynamic named range comprising the dates in column A

Or in VBA

Sub aTest()
    'Gets the 4th largest unique date in Range A2:A?
    Dim myDate As Long
    
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Name = "DataRange"
    myDate = Evaluate("=LARGE(IF(FREQUENCY(DataRange,DataRange),DataRange),4)")
    MsgBox myDate
End Sub

Hope this helps

M.