Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format of cells with formula in them.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format of cells with formula in them.
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 "NDBC" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format of cells with formula in them.
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 "Jacob Skaria" wrote: 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 "NDBC" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format of cells with formula in them.
--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 "NDBC" wrote: 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 "Jacob Skaria" wrote: 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 "NDBC" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA format of cells with colour based on IF formula | Excel Discussion (Misc queries) | |||
Format cells without a formula | Excel Worksheet Functions | |||
Format for formula cells | Excel Worksheet Functions | |||
conditional format cells with formula | Excel Worksheet Functions | |||
Format cells with a formula (7 conditions). | Excel Discussion (Misc queries) |