Extracting information between date and time code
Hi
I am trying to create a macro that will find a date&time, then copy all the lines of data below that date&time (all info is in the same column e.g. A:A), pasting each line of data in the same row as said data&time but with each line of data being pasted into a different column. i.e.
Before:-------------------------- After:
19/07/86 14:08:00-----------19/07/86 14:08:00 | Mr x | received x | received x | received x
Mr x-------------------------------Mr x
received x-----------------------received x
received x-----------------------received x
received x-----------------------received x
However i want it on a loop, so that as soon as it finds another date&time in the same column it starts doing the same again. This has to occur for the entire spreadsheet (all data is in the same column). NB: date and time may be just date and may be in a different format, and information below the date&time (or just date) is not always the same i.e.
Before:---------------------------After:
19/07/86 14:08:00-----------19/07/86 14:08:00 | Mr x | received x | received x | received x
Mr x------------------------------- Mr x
received x-----------------------received x
received x-----------------------received x
received x-----------------------received x
22/07/86 08:23:53-----------22/07/86 08:23:53 | Mr y | reposted y | received y |
Mr y-------------------------------Mr y
reposted y----------------------reposted y
received y----------------------received y
01-Oct-87-----------------------01-Oct-87 | Mr z | received z | received z | No del z | received z
Mr z-------------------------------Mr z
received z-----------------------received z
received z-----------------------received z
No del z-------------------------No del z
received z----------------------received z
After this i would like to turn the row with the date&time and pasted data to be formatted orange so that i can extract this information into another sheet based on the colour of the row. Once this is done i have a macro to extract all orange rows into another sheet.
I'm pretty stuck on this one (partly as i cant get the macro to identify all the different date and time formats i am using and i cant figure out why). Any help anyone could give me would be VERY much appreciated.
Thanks
Mattdave86
This macro will merge all data below a date this cell and change its color to orange. Yoou might have to set the exact same color your other macro uses, though.
Public Sub Merge2_1line() Dim C_ell As Range, R_orig As Range For Each C_ell In Range("A1", Cells(Rows.Count, 1).End(xlUp)) If VarType(C_ell) = vbDate Then C_ell.Interior.Color = 49407 Set R_orig = C_ell Else R_orig = R_orig & " " & C_ell End If Next End Sub
Last edited by p24leclerc; 10-03-2011 at 07:40 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks