Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel keeps converting my %s to Time format
Hi,
I'm using Excel 97. I have a vba function that puts values in specific cells. To do so, I'm naming each cell and referencing it like so: dim varMyPercent as variant varMyPercent = 0.02 wb.Names("metric1000").RefersToRange.Value = varMyPercent Here, the cell referred to by metric1000 is already formatted as a % with 0 decimal points. The problem is that Excel seems to (somewhat randomly) convert the cell to a time format (e.g. 0.02 gets displayed as 12:28:48 AM) I can do the above algorithm for 100 cells and perhaps 2-5 cells will get converted display in the time format. There doesn't seem to be any consistency as to how Excel does this and it's definitely not wanted. Any ideas how to prevent this from happening? I tried prefixing the value with an apostrophe but this causes Excel to ignore the formatting in the cell. Thanks, Michael D |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel keeps converting my %s to Time format
I'd make su
with wb.Names("metric1000").RefersToRange .numberformat = "0.0%" 'or whatever .Value = varMyPercent end with Michael Deathya wrote: Hi, I'm using Excel 97. I have a vba function that puts values in specific cells. To do so, I'm naming each cell and referencing it like so: dim varMyPercent as variant varMyPercent = 0.02 wb.Names("metric1000").RefersToRange.Value = varMyPercent Here, the cell referred to by metric1000 is already formatted as a % with 0 decimal points. The problem is that Excel seems to (somewhat randomly) convert the cell to a time format (e.g. 0.02 gets displayed as 12:28:48 AM) I can do the above algorithm for 100 cells and perhaps 2-5 cells will get converted display in the time format. There doesn't seem to be any consistency as to how Excel does this and it's definitely not wanted. Any ideas how to prevent this from happening? I tried prefixing the value with an apostrophe but this causes Excel to ignore the formatting in the cell. Thanks, Michael D -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel keeps converting my %s to Time format
Hi,
Try ... wb.Names("metric1000").RefersToRange.Numberformat= "0%" wb.Names("metric1000").RefersToRange.Value = varMyPercent HTH "Michael Deathya" wrote: Hi, I'm using Excel 97. I have a vba function that puts values in specific cells. To do so, I'm naming each cell and referencing it like so: dim varMyPercent as variant varMyPercent = 0.02 wb.Names("metric1000").RefersToRange.Value = varMyPercent Here, the cell referred to by metric1000 is already formatted as a % with 0 decimal points. The problem is that Excel seems to (somewhat randomly) convert the cell to a time format (e.g. 0.02 gets displayed as 12:28:48 AM) I can do the above algorithm for 100 cells and perhaps 2-5 cells will get converted display in the time format. There doesn't seem to be any consistency as to how Excel does this and it's definitely not wanted. Any ideas how to prevent this from happening? I tried prefixing the value with an apostrophe but this causes Excel to ignore the formatting in the cell. Thanks, Michael D |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel keeps converting my %s to Time format
Thanks Dave,
That seems like it should work but I'd like to avoid it if possible. I have almost 1000 such cells, each with their own format and I'd like to be able have some flexibility without having the formats centrally managed like that. I.e. if someone wants a number formatted differently, they should be able to change it in the cell properties without having to get involved with VBA. Why can't Excel just leave the formats alone? It drives me nuts that I can't type "1-3" without it converting it to Jan-3. Surely there must be a way to turn off this 'feature'! :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel keeps converting my %s to Time format
I've seen excel change formats from General to Date (or Time), but I've never
seen it change from Percent to time. The way I turn the feature off for 1-3 becoming Jan-3 is to format the cell as text (or add that leading apostrophe (both of which you don't like...) Michael Deathya wrote: Thanks Dave, That seems like it should work but I'd like to avoid it if possible. I have almost 1000 such cells, each with their own format and I'd like to be able have some flexibility without having the formats centrally managed like that. I.e. if someone wants a number formatted differently, they should be able to change it in the cell properties without having to get involved with VBA. Why can't Excel just leave the formats alone? It drives me nuts that I can't type "1-3" without it converting it to Jan-3. Surely there must be a way to turn off this 'feature'! :) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text format of time/date into Excel time/date for subtr | Excel Worksheet Functions | |||
Converting time to decimal format | Excel Worksheet Functions | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
Converting Text to Time format of XX:XX | Excel Discussion (Misc queries) | |||
Converting to Time Format | Excel Discussion (Misc queries) |