VBA to set date range?

[Tgbell]

Hello all,

I am using a current macro that pulls from a column of set range of dates. Is there a way to set a standard date so 2019 dates will show up 1/1/2019-12/31/2018 same with 2020 and so on filling in. I do not know if this is in excel settings or something I will have to call in vba.

Thanks

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Tgbell]

I just added it above, to reiterate to hopefully explain better: I am using

Please Login or Register  to view this content.

which I have number formatted to show year, however it shows from today so 7/10/2019-7/10/2020 ( a year after current day). This messes up the pull from the function because it is supposed to be pulling all 2019 dates (1/1/2019-12/31/2019), the code shows more, I did not know how to really add the after besides explaing it. The after should just show the correct date range in year.

[Richard Buttrey]

Sorry but I find the explanation confusing.

Remember you are intimately au fait with your data and terminology, know what it is and know what results it should give and where.
We don't. We start from zero knowledge of your system and requirement so you need to be absolutely precise and explain in detail making reference to specific cells all the time.

You should show exactly what you start with, and manually add in another area exactly what you want to see after any formulae or actions have been applied. Explain step by step how you use this workbook.

[Tgbell]

Rich,
The process goes like this: I copy each day's data and paste on Sheet2, I then run my macro with the formulas you can see, and it calculates min,max, so on (on notes page). These formulas reference the dates on Sheet 2 (column E and G), and compare them with the dates in column A on Notes. By comparing them, it is putting in information and calculating by separating the items on Sheet 2 by company and year. The data validation table (on the right of Notes gives the option to select one company or a few. When my formulas CURRENTLY reference the date (which is in years) it refers to 7/10/2019-7/10/2020 which is from column A. When I run the current macro, some dates for 2020 show up in 2019 since it is a messed up date range.

Since on sheet2 the dates could be totally random within a year, I need my code in Column A to have a standard year, by formula, so 1/1/2019-12/31/2019 and fill down. This will now sort my data correctly when my macro runs.

The finished product should pull the correct years for the company because the years will be the entire year, not today and a year from today.

[JeteMc]

If you only want to pull data between January 1st and December 31st of a given year then you could modify the formula for "MIN" (Cell B3) so that it reads:

Please Login or Register  to view this content.

Let us know if you have any questions.