PDA

View Full Version : Changing/retaining format of cells



chamster
09-04-2007, 12:06 AM
I tried the following code

Union(.Range(.Cells(2, 8), .Cells(periods + 1, 8)), .Range(periods + 3, 8, 2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"

as well as a not so sophisticated version

.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"

and in both cases the area gets selected BUT the format doesn't stay (or isn't changed - i don't know which. Why and how do i kill it?

On a very similar subject - i try to copy a range and paste it in INCLUDING the formatting. What i do is the following.

With ActiveSheet
.Range(.Cells(1, 1), .Cells(periods, 1)).Select
Selection.Copy
.Range(.Cells(2, 8), .Cells(periods + 1, 8)).Select
.Paste
.Range(.Cells(periods + 3, 8), .Cells(2 * periods + 3, 8)).Select
.Paste
.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"
End With


For some reason (which i'm sure i'll be quickly made aware of soon) the formatting dosn't follow the copy process. How do i enforce it?

Charlize
09-04-2007, 12:35 AM
You could try this one (not tested)With ActiveSheet
.Range(.Cells(1, 1), .Cells(periods, 1)).Copy
.Range(.Cells(2, 8), .Cells(periods + 1, 8)).PasteSpecial xlPasteAll
.Range(.Cells(periods + 3, 8), .Cells(2 * periods + 3, 8)).Copy
.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).PasteSpecial xlPasteAll
End With

chamster
09-04-2007, 12:40 AM
I'll try that right away. Thanks!

Now, to the first part of the question. How come the formatting doesn't work the way i'd expect? Using your approach i can solve my problem for today but i like to feel that i'm the one in control even tomorrow, not the stupid piece of silicon... :)

Also, i just realized that i'm changing the format anyway. In the original cells one can see yyyy-mm-dd hh:mm:ss but i only wish to present the hh:mm format. So, the tomorrow issue has just become todays issue. More suggestions, please.

rory
09-04-2007, 03:14 AM
wHAT

rory
09-04-2007, 03:15 AM
Let's try that again! :)

What is the format supposed to do? That is, apparently, the equivalent of the built-in time format hh:mm in the Catalan region.

chamster
09-04-2007, 04:49 AM
Perhaps i didn't do a great job explaining my pains. Here it goes:

I have a column on form
2007-05-02 07:00:00
2007-05-02 07:05:00
2007-05-02 07:10:00
2007-05-02 07:15:00
...
2007-05-08 09:40:00
2007-05-08 09:45:00
2007-05-08 09:50:00
2007-05-08 09:55:00
and since i'm cutting the data placing each new day TO THE RIGHT (as in "even-slice-and-place-nice") i only need the labels for hours, not the days/months/years.

So, i copied the time stamps of the first day and pasted them in front of the newly created tables of slices. Now, i need to ditch the date part retaining only time part from it.

As i do it, i get "########" in the cells. The data is right because i can then pick the format manually and everything is right. The problem is that i need it to be automated...

rory
09-04-2007, 05:01 AM
In that case you only want "hh:mm" as a number format and it should work, assuming your dates are actually stored as dates.

chamster
09-04-2007, 07:25 AM
Are you saying that i should skip the comma and at characters? What do they do?

rory
09-04-2007, 08:06 AM
I'm assuming that your system uses the comma to separate the format sections (mine uses semicolons). The @ symbol is the text symbol.
Number formats are broken down into up to 4 sections:
1. Positive numbers
2. Negative numbers
3. Zero values
4. Text values