Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling Data from Range
Not sure if there is an easy way to do this, I want to pull certain info
from a range Physical Therapy 12/16/03-12/16/03 on this day Occupational Therapy on this day 12/17/03-12/17/03 12/17/03-12/17/03 Treatment I want to pull the dates on each one of these "12/17/03-12/17/03", etc. The only thing I see that is static the date format and the "-" between the dates. Can someone point me in the right direction? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling Data from Range
I assume you want to do this in VBA, right? As long as the date format is
constant, mm/dd/yy-mm/dd/yy, you should be able to do this. Assuming all entries apply to just one day and you want only a list of the treatments on that specified day, Dim TheDate AS Date Dim DateText As String Dim Cell AS Range Dim X As Variant Dim i As Long Dim j As Long Dim Treatments() As String TheDate = #12/16/2003# DateText =Format$(TheDate,"mm/dd/yy") DateText2 = DateText & "-" & DateText For Each Cell in Range("A1:A100") X = Cell.Value i = Instr(X, DateText) If i Then X = Replace(X, DateText2, "") X = Replace(X, DateText, "") X = Replace("on this day", "") X = Application.Trim(X) j = j + 1 Redim Preserve Treatments(1 to j) Treatments(j) = X End If Next Cell OTOH, if your goal is to get a list of all dates on which, e.g., physical therapy was given, and (a) *every* entry has a date in "mm/dd/yy-mm/dd/yy" format, and (b) the only dash is the one between the dates, then Dim Cell As Range Dim X As Variant Dim i As Long Dim j As Long Dim DateList() As String Dim Rx As String Rx = "Physical Therapy" For Each Cell in Range("A1:A100") X = Cell.Value If Instr(1, X, Rx, vbTextCompare) Then i = Instr(X, "-") 'the next line will never fail if the format is as you show If i = 9 Then X = Mid$(X, i - 8, 17) j = j + 1 Redim Preserve DateList(1 to j) DateList(j) = X End If End If Next Cell Note that if you're looking for a list of activity on a given date, say 12/18/2003, and a single entry can refer to a date span, so it looks like "12/15/03-12/19/03", the first routine will NOT find it. In that case, for every entry you would need to extract the date segment and parse it into the beginning and ending dates, then check whether that date range includes your target date. If you can be more specific about where the range is, what you want to "pull" (rows for a specific date? rows for a specific type of treatment?, the whole line or just part of it? whether the date specified is always just a single date or a date range? etc), what you want to do with the data once it's extracted, we can be of more help. OTOH, maybe you don't need VBA. Maybe you can accomplish this using AutoFilter or Advanced Filter. But I think you would need VBA if the date can be a date range rather than a single date. On Tue, 12 Oct 2004 13:50:19 -0400, "dthmtlgod" wrote: Not sure if there is an easy way to do this, I want to pull certain info from a range Physical Therapy 12/16/03-12/16/03 on this day Occupational Therapy on this day 12/17/03-12/17/03 12/17/03-12/17/03 Treatment I want to pull the dates on each one of these "12/17/03-12/17/03", etc. The only thing I see that is static the date format and the "-" between the dates. Can someone point me in the right direction? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling data from other wb based on data to populate dropdown list | Excel Discussion (Misc queries) | |||
Pulling Data that falls within a specific date range? | Excel Worksheet Functions | |||
Tying to extract all data from a pivot pulling from external data | Excel Discussion (Misc queries) | |||
Linking two spreadsheet, pulling data from one cell to another, data is being truncated | Excel Worksheet Functions | |||
How do I sum the value of a range of cells without pulling any ce. | Excel Discussion (Misc queries) |