Hi,
I'm pretty new to VBA and I've got this problem with some data I'd like to format.
The data is formatted like this:
Date Time Value
2/10/04 22:00 25
2/10/04 23:00 26
2/11/04 00:00 27
2/11/04 1:00 28
2/11/04 2:00 29
....
2/15/04 1:00 30
2/15/04 2:00 31
The data continues for several more days for about 200-300 rows.
I'd like to be able to enter in a time period less than 24 hours (i.e. from 7:00am to 5:00pm or 10:00pm to 5:00am) into an inputbox and have excel extract the rows within the specified time period and copy it into another sheet.
Eventually, I'd like to transpose the data in the new sheet to look like this:
Day 2/10/04 2/11/04 2/12/04
Time
7:00 25 25
8:00 26 26
9:00 27 etc.
10:00 28
11:00 29
For the sake of simplicity, I'm just interested in figuring out how to select rows within the time range. I might be able to figure out the transpose thing later or ask again in the forum. I've found this code somewhere online and tried using it to find the range and copy it over to a new sheet:
aCell, bCell as Range
Start, Off as String
Start = CDate(InputBox("Start:"
)
Set aCell = Columns(3).Find(Start, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns)
Off = CDate(InputBox("End:")
Set bCell = Columns(3).Find(Off, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns)
Worksheets("Sheet2".Cells.ClearContents
Rows(aCell.Row & ":" & bCell.Row).Copy _
Destination:=Worksheets("Sheet2".Range("A1"
This doesn't seem to work. I think my problem has something to do with time formats and the fact that sometimes the time range will span two dates. I also don't have any way to have it loop to select time ranges for other dates.
I hope this question isn't too confusing. Any help or advice would be greatly appreciated. Thanks.
Everest
I'm pretty new to VBA and I've got this problem with some data I'd like to format.
The data is formatted like this:
Date Time Value
2/10/04 22:00 25
2/10/04 23:00 26
2/11/04 00:00 27
2/11/04 1:00 28
2/11/04 2:00 29
....
2/15/04 1:00 30
2/15/04 2:00 31
The data continues for several more days for about 200-300 rows.
I'd like to be able to enter in a time period less than 24 hours (i.e. from 7:00am to 5:00pm or 10:00pm to 5:00am) into an inputbox and have excel extract the rows within the specified time period and copy it into another sheet.
Eventually, I'd like to transpose the data in the new sheet to look like this:
Day 2/10/04 2/11/04 2/12/04
Time
7:00 25 25
8:00 26 26
9:00 27 etc.
10:00 28
11:00 29
For the sake of simplicity, I'm just interested in figuring out how to select rows within the time range. I might be able to figure out the transpose thing later or ask again in the forum. I've found this code somewhere online and tried using it to find the range and copy it over to a new sheet:
aCell, bCell as Range
Start, Off as String
Start = CDate(InputBox("Start:"
)Set aCell = Columns(3).Find(Start, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns)
Off = CDate(InputBox("End:"
)Set bCell = Columns(3).Find(Off, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns)
Worksheets("Sheet2"
.Cells.ClearContentsRows(aCell.Row & ":" & bCell.Row).Copy _
Destination:=Worksheets("Sheet2"
.Range("A1"This doesn't seem to work. I think my problem has something to do with time formats and the fact that sometimes the time range will span two dates. I also don't have any way to have it loop to select time ranges for other dates.
I hope this question isn't too confusing. Any help or advice would be greatly appreciated. Thanks.
Everest
