PDA

View Full Version : Solved: Extracting Data Based on Date Range

kathyb0527
01-15-2009, 02:20 PM
I am working on a project where I take the data from one worksheet, put it in another, clean up the data, calculate the days between steps in the process and then chart it. I've accomplished most of it (still need error trapping and some little tweaks), but now managment wants to be able to pull the data for a user specified date range. Meaning, although the data is for a whole year, they might just want the first six months.

Now I'm not sure what my next step is since I see problems with all three ideas I had :
Change how the data is copied and pasted (Already slow, would this slow it down more?)
Filter the data I have (Filtering based on dates can be...unreliable)
Delete rows based on cell values (I know it can be done but I don't know how to do it)

If someone could give me a push in the right direction, it would be greatly appreciated.

I've attached my spreadsheet with associated macros.

Bob Phillips
01-15-2009, 04:59 PM
Which date is being referred to?

Filtering on dates is okay, as long as you understand its quirks.

kathyb0527
01-16-2009, 09:32 AM
We would be looking at data based on Lab End Date.

kathyb0527
01-19-2009, 10:00 AM
I tried filtering the dates, but it doesn't affect the outcome.

lucas
01-19-2009, 03:55 PM
To get it down to just being able to filter by the month:

Brute force for column G Actual Lab ending date

Option Explicit
Sub Macro4()
Dim cell As Range
Columns("H:H").Insert Shift:=xlToRight
For Each cell In ActiveSheet.Range("G1", ActiveSheet.Range("G" & ActiveSheet.Rows.Count).End(xlUp))
Worksheets("Metric Data").Columns("H").NumberFormat = "mm yyyy"
If cell <> "" Then
cell.Offset(0, 1) = "=" & cell.Address & ""
End If
Next cell

ActiveSheet.Range("H:H").Copy
ActiveSheet.Range("H:H").PasteSpecial Paste:=xlValues
Columns("H:H").Columns.AutoFit

Columns("G:G").Delete Shift:=xlToLeft
End Sub

kathyb0527
01-20-2009, 01:33 PM
Hi Lucas,
Thanks for the reply. Unfortunately, I need to calculate the number of days between each step in the process and by changing the date to month and year, I can't get that information.

I think what I would like to do is step through column G and delete rows based on the user input, but I'm not sure how to do that.

kathyb0527
02-03-2009, 02:53 PM
Can anyone give me any other suggestions on this problem? Thanks!

mdmackillop
02-04-2009, 11:00 AM

kathyb0527
02-05-2009, 10:06 AM
Thanks, here it is.

mdmackillop
02-05-2009, 02:11 PM
Add to a userform with two textboxes ands a button

Private Sub CommandButton1_Click()
For Each cel In Intersect(Columns(7), ActiveSheet.UsedRange)
If cel >= CDate(TextBox1.Text) And cel <= CDate(TextBox2.Text) Then
cel.Offset(, 11) = "x"
End If
Next
ActiveSheet.Columns(18).AutoFilter Field:=1, Criteria1:="<>"
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Application.Max(Sheets("Metric Data").Columns(7)) - 180, "dd/mm/yy")
TextBox2.Text = Format(Application.Max(Sheets("Metric Data").Columns(7)), "dd/mm/yy")
End Sub

kathyb0527
02-06-2009, 01:21 PM
I haven't worked with UserForms before so forgive me if these are basic questions.
How do I get the userform to open and close?
Does the Userform_Initialize force the format of the text box?

Thanks!

Bob Phillips
02-06-2009, 02:04 PM
I haven't worked with UserForms before so forgive me if these are basic questions.
How do I get the userform to open and close?

In a macro, show it

Userform1.Sow

Does the Userform_Initialize force the format of the text box?

Not implicitly, you have to format it.