CountIF VBA, financial year question

[RhodEvans]

Afternoon all,

I have hit a definite block in my understanding of VBA. I have been trying to get it to do something I would assume to be simple all day, but don't seem to have the knowledge or understanding to even get close. I would be really grateful for any help that you could give (or possible a link if this question has already been asked).

In column K of a spreadsheet (starting in row 4) I have a list of dates in the format dd/mm/yy. I would like a VBA routine do do a count on the column for all dates that fall within a certain financial year that the user has chosen from a list box (listbox3) in a user form (without destoying the data in the cells). The dates are in the format 2013-14 as an example and then store in a variable for later use.

As said earlier I am sure this is simpler than I think it is, but I would still be immensly grateful for any help you wonderful people could supply.

Thanks in advance

Rhod

 

[ChrisM]

You can do this a couple ways, but the easiest is using an array formula in the worksheet to calculate the results. You could hide it off to the side, then read the results back in to your user form. Here's the formula, entered with ctrl-shift-enter:

=SUM(IF(YEAR(K5:K9)=N7,1,0))

Where K5:K9 are your dates, and N7 is your desired year criteria (in your case you would need to write the listbox year to the worksheet first)

Or, you can do all that in VBA directly, using the Evaluate function, like this:

Sub test()

Dim lcount As Long
Dim yr As Long

yr = 2013

lcount = Evaluate("=SUM(IF(YEAR(K5:K9)=N7,1,0))")

MsgBox lcount

End Sub

You would want to go in and edit that formula, replacing N7 with a Listbox.Value variable.

 

[CharlesChuckieCharles]

Just a point when the user selects 2013-14 where are the real start/end dates for that year as in 06 April 2013 to 5 April 2014 ?

 

[RhodEvans]

Apologies for the delay in answering your question. I was away from the beloved technology (I swear I was getting heart palpatations). Yes it is the dates you put in your last post.
I like the idea Chris M gave (Thanks very much for your help), but it doesn't take into account that it is against e.g. 2013-14 rather than just e.g. 2013.
Is the best way round this with a variable definded by an IF statement e.g.

IF Listbox3.value = "2013-14" then
year = 2013

though I would like a way to get round hard coding it and this doesn't take into account that it could be second half of 2013 ir fisrt half of 2014.

i think I am in way over my head, so any help would be met with Joy and immense gratitude on my part!

 

[ChrisM]

Oh sorry, I took 2013-14 to mean 2013 or 2014, not literally with a dash between them. Need some more sample data showing the potential combinations in the listbox. Are they all in format year1-year2, or are there some single year choices? As Charles asked, if there really are underlying start/stop dates, what controls those dates?

Maybe provide some samples in this format: Listbox choice; underlying date range you want the actual count performed on

 

[RhodEvans]

Thanks for your continued support.

the list box will always be in the following format. So far I have set it for a ten year run from 2010, so:

2010-11
2011-12
2012-13
2013-14
2014-15
2015-16
2016-17
2017-18
2018-19
2019-20

The date for the count to be performed on is in the range K4-K1000 (or further if needed) in the format dd/mm/yy. All dagtes are after 1st April 2010.

To make it easier (possibly???) the year can always run from 1st April until 31st March.

Unfortunatly I can't add a copy of the data as it is restricted/confidential information, though if needed I could make up some junk data to help.
The general Idea is that the count would be added to a 'summary report' for anonomous distribution for statistics purposes.

I do apologise. I thought when I originally asked this question, that it was easy and it was just my basic understanding of VBA that was standing in the way, but now I'm thinking that what seems like a simple idea/premise may be anything but.

Once again, thank you for your continued support.

 

[ChrisM]

This can be done with normal spreadsheet formulas, again which you could do off to a hidden cell, or you could replicate this in VBA.

The worksheet solution would require you to output the listbox choice to a cell in the sheet, either manually via the Listbox_Change event, or you could use the LinkedCell property (my choice).

here's the worksheet solution, assuming the listbox outputs to A1:

=COUNTIFS(K4:K1000">=" &DATE(LEFT(A1,4),4,1),K4:K1000,"<=" & DATE("20" & RIGHT(A1,2),3,31))