PDA

View Full Version : [SOLVED] List Data Between Two Dates



martellb
07-04-2018, 07:46 AM
Hi, I'm very new to Visual Basic.

I have an excel sheet containing a column of dates (C8:C17514) with each date being repeated 48 times (amongst other data).

I would like to create a script which by pressing commandbutton1 will list the all the dates including repeats between a selected start date and end date (chosen from combo boxes 1 and 2 respectively) in another column.

Thanks in advance

MINCUS1308
07-04-2018, 11:17 AM
can you post a workbook?

MINCUS1308
07-04-2018, 11:26 AM
Attached is my workbook: 22516


Sub InterestingTest()
FirstDate = CDate("1/1/1900")
SencondDate = CDate("1/1/2020")
MyOutputString = ""

For i = 8 To 17514
CurrentValue = Cells(i, "C").Value

If Not IsDate(CurrentValue) Then GoTo NextIteration

If (CurrentValue > FirstDate And CurrentValue < SencondDate) Or (CurrentValue < FirstDate And CurrentValue > SencondDate) Then
MyOutputString = CurrentValue & vbNewLine & MyOutputString
End If

NextIteration:
Next i

MsgBox MyOutputString
End Sub

I'm not sure if that's what you meant

You can point FirstDate and SecondDate to the respective ComboBoxes and it will spit out a list of every date listed between the given date (in the given set of data)
You can also call this Sub with the On_Click event in the form's module

martellb
07-04-2018, 11:46 AM
That looks like that's almost right. I do however want the selection to be inclusive of the two chosen dates and i want it to output into a given column. I have attached an example worksheet for more clarity.

22518

MINCUS1308
07-04-2018, 12:11 PM
For inclusion I will replace the < and > operands with <= and >=.
Not sure why you need the ActiveX controls for the drop downs - Using data validation is probably easier.
And shapes are easier to use as buttons in my opinion.

I made modifications to the file:
22519


Sub InterestingTest()
FirstDate = CDate(Cells(3, "I").Value)
SencondDate = CDate(Cells(4, "I").Value)

If FirstDate = SencondDate Then MsgBox "same dates bro"

Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents

j = 8

For i = 8 To 17514
CurrentValue = Cells(i, "B").Value

If Not IsDate(CurrentValue) Then GoTo NextIteration

If (CurrentValue >= FirstDate And CurrentValue <= SencondDate) Or (CurrentValue <= FirstDate And CurrentValue >= SencondDate) Then
Cells(j, "H").Value = CDate(Cells(i, "B"))
Cells(j, "I").Value = Cells(i, "C")
Cells(j, "J").Value = Cells(i, "D")
'if you are doing more than 3 or 4 of these there are better ways to do it
'if your data set is less than 6000 lines this will be fine

j = j + 1
End If

NextIteration:
Next i
End Sub

martellb
07-05-2018, 01:05 AM
Thanks a lot man, that works perfectly but you're rightit is a bit slow when I added it to my larger data set. Any ideas ofperformance improvements?

haha I used ActiveX controls because I don't know what I’m doing but I populatedthem with a code that extracts only unique values from the set which was neededcos otherwise it would be filled with 48 copies of 365 dates.
Forcing myself to use VB for tasks that probably don’tneed it as a way of learning.

MINCUS1308
07-05-2018, 05:09 AM
Using arrays is marginally faster if you are gathering, calculating, or moving large sets of data.
You can also suspend some application operations that will help. (Like Application.ScreenUpdating = False)

I'm glad I was able to help you with your problem.
If its all wrapped up will you mark this thread as solved?
[Top right corner of the thread>Thread Tools>Mark as Solved]

Welcome to the Forum!

p45cal
07-05-2018, 09:50 AM
Try the attached to see if it's faster. (Is it the combo population which is slow or the rest?)

martellb
07-06-2018, 12:46 AM
Try the attached to see if it's faster. (Is it the combo population which is slow or the rest?)

Thank you, that is a lot faster but I does have the downside of requiring those extra 6 cells.

Loading the comboboxes wasn't an issue but they don't automatically laoad when the sheet is opened.

p45cal
07-06-2018, 02:40 AM
Thank you, that is a lot faster but I does have the downside of requiring those extra 6 cells. Those cells can be anywhere, even on a hidden sheet.




Loading the comboboxes wasn't an issue but they don't automatically laoad when the sheet is opened.Well, you can choose to call that sub on sheet activation, or on workbook opening or when any value in the date column is changed. It depends on how often the comboboxes are likely to need updating. If the dates are put there by macro code, why not call that sub at the end of that code?