VB coding for formatting time used in calculations

[Tat]

I am a novice at VB coding so this is probably an
elementary question.

I would like to format a range in [h]:mm: format so that I
can use it in a calculation. Right now I can use the
spreadsheet format but in order to 'activate' the format I
have to doubleclick EACH cell and then move to another
cell. There are 200 lines of data that needs to be
formatted and 'activated' so I thought a VB routine would
be more efficient.

This is what I have come up with. I have no trouble with
formatting it, it's the coding for the 'doubliclick'that I
am having problem with. Coding example:

Sub DirectTimetoCalculateTimeFormat()
'
Columns("D").Select
Selection.NumberFormat = "[h]:mm:"
End Sub

Sub DoubleClicks()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("d8:e300")
For Each c In myrange.cells
Application.DoubleClick
Application.MoveAfterReturn = TRUE
Application.MoveAfterReturnDirection = xlDown
Next
End Sub

From what I have read the 'For Each..Next' loop is a good
statement to use or is there another way of coding this
altogether?

I would like to use the same procedure for different
spreadsheets so I have saved it in personal.xls. Do I have
to define the spreadsheet in the procedure or is it
assumed when I open the Macro (saved in personal.xls)in the
current spreadsheet?

 

[Jake Marx]

Hi Tat,

Typically, when a value is not "recognized" until you double-click, that
means Excel has not evaluated the entry as a number yet. Times/dates are
stored as numbers in Excel. To force Excel to reevaluate the cells without
having to double-click each one, you can do this:

1) enter 0 in an empty cell
2) copy that cell
3) select the range to reevaluate
4) paste special paste=values and operation=add

That will, in effect, add 0 to each cell in the range, which will force
Excel to recognize the date/times. You could record a macro while doing
this if you need a programmatic solution.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Thank you, thankyou, thankyou! It works wonderfully!

-----Original Message-----
Hi Tat,

Typically, when a value is not "recognized" until you double-click, that
means Excel has not evaluated the entry as a number yet. Times/dates are
stored as numbers in Excel. To force Excel to reevaluate the cells without
having to double-click each one, you can do this:

1) enter 0 in an empty cell
2) copy that cell
3) select the range to reevaluate
4) paste special paste=values and operation=add

That will, in effect, add 0 to each cell in the range, which will force
Excel to recognize the date/times. You could record a macro while doing
this if you need a programmatic solution.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I am a novice at VB coding so this is probably an
elementary question.

I would like to format a range in [h]:mm: format so that I
can use it in a calculation. Right now I can use the
spreadsheet format but in order to 'activate' the format I
have to doubleclick EACH cell and then move to another
cell. There are 200 lines of data that needs to be
formatted and 'activated' so I thought a VB routine would
be more efficient.

This is what I have come up with. I have no trouble with
formatting it, it's the coding for the 'doubliclick'that I
am having problem with. Coding example:

Sub DirectTimetoCalculateTimeFormat()
'
Columns("D").Select
Selection.NumberFormat = "[h]:mm:"
End Sub

Sub DoubleClicks()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("d8:e300")
For Each c In myrange.cells
Application.DoubleClick
Application.MoveAfterReturn = TRUE
Application.MoveAfterReturnDirection = xlDown
Next
End Sub

From what I have read the 'For Each..Next' loop is a good
statement to use or is there another way of coding this
altogether?

I would like to use the same procedure for different
spreadsheets so I have saved it in personal.xls. Do I have
to define the spreadsheet in the procedure or is it
assumed when I open the Macro (saved in personal.xls)in the
current spreadsheet?

.