LinkBack URL
About LinkBacks
Hello all,
I have a column of time values hh:mm:ss
I am trying to format the time to hh:mm and round the time values to the nearest 5 mins. Anybody know how to do this with vba?
Hello all,
I have a column of time values hh:mm:ss
I am trying to format the time to hh:mm and round the time values to the nearest 5 mins. Anybody know how to do this with vba?
Last edited by rtcwlomax; 07-02-2015 at 03:55 PM.
I'm also thinking to incorporate an input box that asks user to input the nearest whole minute value that the vba should round up or down to if possible.
Sub f_time() ' ' format_time Macro ' Dim timeValue As Variant timeValue = InputBox("Input time") Cells.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = "h:mm" '////////////////////////////////////////////////////////// 'Missing vba to round to nearest timeValue in mins '////////////////////////////////////////////////////////// End Sub
Dim cell As Range Dim iMin As Long iMin = 5 For Each cell In Selection.Cells cell.Value = Round(cell.Value2 * 1440 / iMin, 0) * iMin / 1440 Next cell
Entia non sunt multiplicanda sine necessitate
Thanks shg,
Returned error in the code. Type mismatch.
I have attached WB with Time column and macro. Would appreciate if you could take a look
Sub f_time1() Dim cell As Range Dim iMin As Long iMin = InputBox("Input time") Cells.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Range(Selection, Selection.End(xlDown)).Select For Each cell In Selection.Cells cell.Value = Round(cell.Value2 * 1440 / iMin, 0) * iMin / 1440 Next cell Selection.NumberFormat = "h:mm" End Sub
Last edited by rtcwlomax; 07-02-2015 at 04:43 PM.
What happens when you step through the code, what are the relevant cell values?
Time to spend some quality time at http://www.cpearson.com/excel/DebuggingVBA.aspx
it come up with error on following line:
cell.Value = Round(cell.Value2 * 1440 / iMin, 0) * iMin / 1440
it come up with error on following line:
cell.Value = Round(cell.Value2 * 1440 / iMin, 0) * iMin / 1440
And the cell value is ...?
Hi,
You could use a standard Excel formula. e.g.
Formula:
=MROUND(A1*1440,5)/1440
where A1 is the cell with the time.
In VBA you could loop down the column and use the formula above in an Application.WorksheetFunction.MROUND....etc instruction and write the value back to the cell.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks for suggestions and thanks shg for forcing me to do some debug which I have never tried before.
Here is my solution. I'm not sure of any other way to select the cells below my search value but it works
Sub f_time1() Dim cell As Range Dim iMin As Long iMin = InputBox("Input time") Cells.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Selection.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select For Each cell In Selection.Cells cell.Value = Round(cell.Value2 * 1440 / iMin, 0) * iMin / 1440 Next cell Selection.NumberFormat = "h:mm" End Sub
Good job.
You will learn that it is almost never necessary to select cells or worksheets to operate on them, and doing so slows your code. But in the meanwhile, watching your code operate and, especially, learning to debug, are much more important.
Last edited by shg; 07-02-2015 at 08:24 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks