kathyb0527
04-28-2008, 11:35 AM
Hi everyone,
I have a macro that takes columns from multiple worksheets and puts them in one worksheet for a client. The problem I have is that the timepoint format that comes out of our database is xXX:XX:XX which the client wants reported as Day X hour x. I've written the code as
With rTimeptCol
.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:00:00", Replacement:="Day 1, Pre-dose", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:00:30", Replacement:="Day 1, 0.5 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:01:00", Replacement:="Day 1, 1 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:01:30", Replacement:="Day 1, 1.5 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:02:00", Replacement:="Day 1, 2 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:03:00", Replacement:="Day 1, 3 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:04:00", Replacement:="Day 1, 4 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:06:00", Replacement:="Day 1, 6 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:08:00", Replacement:="Day 1, 8 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:10:00", Replacement:="Day 1, 10 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:12:00", Replacement:="Day 1, 12 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="003:00:00", Replacement:="Day 2, 24 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="005:00:00", Replacement:="Day 3, 48 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="007:00:00", Replacement:="Day 4, 72 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="009:00:00", Replacement:="Day 5, 96 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="011:00:00", Replacement:="Day 6, 120 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="013:00:00", Replacement:="Day 7, 144 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
which as you can imagine, slows down the code quite a bit. Any help would be greatly appreciated.
Thank you for your time,
kathyb0527
I have a macro that takes columns from multiple worksheets and puts them in one worksheet for a client. The problem I have is that the timepoint format that comes out of our database is xXX:XX:XX which the client wants reported as Day X hour x. I've written the code as
With rTimeptCol
.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:00:00", Replacement:="Day 1, Pre-dose", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:00:30", Replacement:="Day 1, 0.5 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:01:00", Replacement:="Day 1, 1 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:01:30", Replacement:="Day 1, 1.5 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:02:00", Replacement:="Day 1, 2 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:03:00", Replacement:="Day 1, 3 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:04:00", Replacement:="Day 1, 4 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:06:00", Replacement:="Day 1, 6 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:08:00", Replacement:="Day 1, 8 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:10:00", Replacement:="Day 1, 10 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="001:12:00", Replacement:="Day 1, 12 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="003:00:00", Replacement:="Day 2, 24 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="005:00:00", Replacement:="Day 3, 48 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="007:00:00", Replacement:="Day 4, 72 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="009:00:00", Replacement:="Day 5, 96 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="011:00:00", Replacement:="Day 6, 120 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="013:00:00", Replacement:="Day 7, 144 hr", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
which as you can imagine, slows down the code quite a bit. Any help would be greatly appreciated.
Thank you for your time,
kathyb0527