Format of cells with formula in them.

[NDBC]

I have a cell with a formula in it that subtracts two times that are
formatted [hh]:mm:ss. The formula works fine but the answer doesn't seem to
come out in the same format. Is there a way to set a cell so that it is in
the same format as its dependents. The dependents are on a different
worksheet.

If not here is my dilema. I have an unlimited number of columns which hold
race times and current place in the race. The columns go time, place, time,
place etc for as many laps as get done in the race. My trouble is i need the
time cells formatted [hh]:mm:ss and the place cells formatted general. The
only way I can think of formatting every second column as time quickly is to
write a sub along the following lines.

For n =1 to 255 (or however many columns there are) step 2

column (n:n).column.numberformat = [hh]:mm:ss

next n

I realise that isn't quite right but I could work it out if there is not an
easier way. Is there an easier way.

Thanks

 

[Jacob Skaria]

The below code will format all cells with formula to the mentioned format..

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, _
23).NumberFormat = "[hh]:mm:ss"

If this post helps click Yes

 

[NDBC]

Jacob, thanks for the help. I have looked up the vba help and understand that
it is only picking cells with formulas but what does the 23 mean.

Thanks

The below code will format all cells with formula to the mentioned format..

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, _
23).NumberFormat = "[hh]:mm:ss"

If this post helps click Yes
---------------
Jacob Skaria

I have a cell with a formula in it that subtracts two times that are
formatted [hh]:mm:ss. The formula works fine but the answer doesn't seem to
come out in the same format. Is there a way to set a cell so that it is in
the same format as its dependents. The dependents are on a different
worksheet.

If not here is my dilema. I have an unlimited number of columns which hold
race times and current place in the race. The columns go time, place, time,
place etc for as many laps as get done in the race. My trouble is i need the
time cells formatted [hh]:mm:ss and the place cells formatted general. The
only way I can think of formatting every second column as time quickly is to
write a sub along the following lines.

For n =1 to 255 (or however many columns there are) step 2

column (n:n).column.numberformat = [hh]:mm:ss

next n

I realise that isn't quite right but I could work it out if there is not an
easier way. Is there an easier way.

Thanks

 

[Jacob Skaria]

--This is optional. (extracted from help) If Type is either
xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine
which types of cells to include in the result. These values can be *** added
**) together to return more than one type. The default is to select all
constants or formulas, no matter what the type. Can be one of the following
XlSpecialCellsValue constants the corresponding values adds up to 23)

xlErrors - 16
xlLogical - 4
xlNumbers - 1
xlTextValues - 2

--Something which I missed to mention. If you dont want to change the format
of all formulas; you can restrict this to a range in the worksheet

Range("D1:J100").SpecialCells(xlCellTypeFormulas, _
23).NumberFormat = "[hh]:mm:ss"


If this post helps click Yes
---------------
Jacob Skaria

Jacob, thanks for the help. I have looked up the vba help and understand that
it is only picking cells with formulas but what does the 23 mean.

Thanks

The below code will format all cells with formula to the mentioned format..

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, _
23).NumberFormat = "[hh]:mm:ss"

If this post helps click Yes
---------------
Jacob Skaria

I have a cell with a formula in it that subtracts two times that are
formatted [hh]:mm:ss. The formula works fine but the answer doesn't seem to
come out in the same format. Is there a way to set a cell so that it is in
the same format as its dependents. The dependents are on a different
worksheet.

If not here is my dilema. I have an unlimited number of columns which hold
race times and current place in the race. The columns go time, place, time,
place etc for as many laps as get done in the race. My trouble is i need the
time cells formatted [hh]:mm:ss and the place cells formatted general. The
only way I can think of formatting every second column as time quickly is to
write a sub along the following lines.

For n =1 to 255 (or however many columns there are) step 2

column (n:n).column.numberformat = [hh]:mm:ss

next n

I realise that isn't quite right but I could work it out if there is not an
easier way. Is there an easier way.

Thanks