Aussiebear
04-16-2010, 11:48 AM
Earlier, I was having an issue getting an advanced Filter to work on sorting data that fell within the Australian financial year, however after a couple of beers, Excel & I have reached agreement with the following workaround.
Cell B11 to LastRow contains a series of dates
In a helper column (Cell N11)
=If(Month(B11)>=7,Year(B11)+1,Year(B11))
But now I was wondering if in the advanced Filter Criteria (with is filled from a form with a series of Textboxes) if txtTransDate.text equals dd/mm/yyyy format then it fills cell B3, or if txtTransDate.text equals yyyy format it fills cell N3. Is this possible?
Sub cmdFilter_Click()
'Set Values to Filter Data on
With Worksheets("Dividends")
.Range("A3").Value = txtASXCode.Text
'Check string format to determine location of textbox input
If txtTransDate.Text = "dd/mm/yyyy" format then
.Range("B3").Value = txtTransDate.Text
else
If txtTransDate.Text = "yyyy"formatthen
.Range("N3").Value= txtTransDate.Text
End If
.Range("C3").Value = txtTransType.Text
.Range("D3").Value = txtUnitPrice.Text
.Range("E3").Value = txtUnitsGained.Text
.Range("H3").Value = txtDivRate.Text
.Range("J3").Value = txtDivEarned.Text
.Range("K3").Value = txtFranking.Text
End With
Call Filter_Dividends
End Sub
The reason being that it could give two choices to advance filter the data on, a particular date or a financial year.
Cell B11 to LastRow contains a series of dates
In a helper column (Cell N11)
=If(Month(B11)>=7,Year(B11)+1,Year(B11))
But now I was wondering if in the advanced Filter Criteria (with is filled from a form with a series of Textboxes) if txtTransDate.text equals dd/mm/yyyy format then it fills cell B3, or if txtTransDate.text equals yyyy format it fills cell N3. Is this possible?
Sub cmdFilter_Click()
'Set Values to Filter Data on
With Worksheets("Dividends")
.Range("A3").Value = txtASXCode.Text
'Check string format to determine location of textbox input
If txtTransDate.Text = "dd/mm/yyyy" format then
.Range("B3").Value = txtTransDate.Text
else
If txtTransDate.Text = "yyyy"formatthen
.Range("N3").Value= txtTransDate.Text
End If
.Range("C3").Value = txtTransType.Text
.Range("D3").Value = txtUnitPrice.Text
.Range("E3").Value = txtUnitsGained.Text
.Range("H3").Value = txtDivRate.Text
.Range("J3").Value = txtDivEarned.Text
.Range("K3").Value = txtFranking.Text
End With
Call Filter_Dividends
End Sub
The reason being that it could give two choices to advance filter the data on, a particular date or a financial year.